package zy.dao.vip.member.impl;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.vip.member.MemberDAO;
import zy.entity.base.brand.T_Base_Brand;
import zy.entity.base.color.T_Base_Color;
import zy.entity.base.emp.T_Base_Emp;
import zy.entity.base.product.T_Base_Product;
import zy.entity.base.shop.T_Base_Shop;
import zy.entity.base.type.T_Base_Type;
import zy.entity.sell.cash.T_Sell_Shop;
import zy.entity.sell.cash.T_Sell_ShopList;
import zy.entity.sell.ecoupon.T_Sell_Ecoupon_User;
import zy.entity.sys.user.T_Sys_User;
import zy.entity.vip.member.T_Vip_ConsumeDetailList;
import zy.entity.vip.member.T_Vip_Member;
import zy.entity.vip.member.T_Vip_Member_Info;
import zy.entity.vip.member.T_Vip_PonitList;
import zy.entity.vip.member.T_Vip_Tag;
import zy.entity.vip.membertype.T_Vip_MemberType;
import zy.entity.vip.trylist.T_Vip_TryList;
import zy.entity.vip.visit.T_Vip_Visit;
import zy.form.NumberForm;
import zy.util.CalendarUtil;
import zy.util.CommonUtil;
import zy.util.DateUtil;
import zy.util.NumberUtil;
import zy.util.StringUtil;

@Repository
public class MemberDAOImpl extends BaseDaoImpl implements MemberDAO{

	@Override
	public Integer count(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object vm_mt_code = params.get("vm_mt_code");
		Object vm_shop_code = params.get("vm_shop_code");
		Object vm_manager_code = params.get("vm_manager_code");
		Object vm_cardcode = params.get("vm_cardcode");
		Object vm_name = params.get("vm_name");
		Object vm_mobile = params.get("vm_mobile");
		Object vm_tag_name = params.get("vm_tag_name");
		Object begin_total_money = params.get("begin_total_money");
		Object end_total_money = params.get("end_total_money");
		Object begin_last_points = params.get("begin_last_points");
		Object end_last_points = params.get("end_last_points");
		String begin_notbuy_day = (String)params.get("begin_notbuy_day");
		String end_notbuy_day = (String)params.get("end_notbuy_day");
		String select_member_type = (String)params.get("select_member_type");
		
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1) FROM (");
		sql.append("SELECT vm_id,vm_code,vm_mobile,vm_cardcode,vm_mt_code,vm_shop_code,vm_name,vm_password,vm_sex,");
		sql.append(" vm_birthday_type,vm_birthday,vm_lunar_birth,vm_state,vm_date,vm_enddate,vm_manager_code,t.companyid,");
		sql.append(" vm_points,vm_used_points,vm_init_points,vm_times,vm_total_money,vm_lastbuy_date,vm_lastbuy_money,");
		sql.append(" (SELECT GROUP_CONCAT(vmt_vt_name) FROM t_vip_member_tag vmt WHERE vmt.vmt_vm_code = t.vm_code AND vmt.companyid = t.companyid GROUP BY vmt_vm_code) AS vm_tag_name ");
		sql.append(" FROM t_vip_member t");
		if(StringUtil.isNotEmpty(select_member_type)){
			sql.append(" LEFT JOIN (");
			sql.append(" SELECT sh_money,buy_times,sh_vip_code ");
			sql.append(" FROM");
			sql.append(" (SELECT IFNULL(SUM(sh_money),0) AS sh_money,SUM(CASE WHEN sh_state=0 THEN 1 WHEN sh_state=1 THEN -1 END )  AS buy_times,sh_vip_code");
			sql.append(" FROM t_sell_shop sh ");
			sql.append(" WHERE sh.sh_state = 0 ");
			sql.append(" AND LENGTH(sh.sh_vip_code)>0 ");
			//活跃客户
			if("1".equals(select_member_type)||"2".equals(select_member_type)||"3".equals(select_member_type)||"4".equals(select_member_type)){
				sql.append(" AND sh.sh_date >= :consumeDay ");
			}
			//休眠客户
			if("5".equals(select_member_type)||"6".equals(select_member_type)||"7".equals(select_member_type)||"8".equals(select_member_type)){
				sql.append(" AND sh.sh_date < :consumeDay AND sh_date >= :lossDay ");
			}
			//流失客户
			if("9".equals(select_member_type)||"10".equals(select_member_type)||"11".equals(select_member_type)||"12".equals(select_member_type)){
				sql.append(" AND sh.sh_date < :lossDay ");
			}
			sql.append(" AND sh.companyid = :companyid ");
			sql.append(" GROUP BY sh.sh_vip_code) as gradetab");
			sql.append(" ) as temp ON temp.sh_vip_code = t.vm_code ");
		}
		sql.append(" JOIN t_base_shop sp ON vm_shop_code = sp_code AND t.companyid = sp.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(" AND t.companyid=:companyid");
		if(StringUtil.isNotEmpty(select_member_type)){
			sql.append(" AND (");
			if("1".equals(select_member_type)){
				sql.append(" t.vm_lastbuy_date >= :consumeDay ");
				sql.append(" AND buy_times >= :loyalVipTimes");
				sql.append(" AND sh_money >= :richVipMoney");
			}
			else if("2".equals(select_member_type)){
				sql.append(" t.vm_lastbuy_date >= :consumeDay ");
				sql.append(" AND buy_times >= :loyalVipTimes");
				sql.append(" AND sh_money < :richVipMoney");
			}
			else if("3".equals(select_member_type)){
				sql.append(" t.vm_lastbuy_date >= :consumeDay ");
				sql.append(" AND buy_times < :loyalVipTimes");
				sql.append(" AND sh_money >= :richVipMoney");
			}
			else if("4".equals(select_member_type)){
				sql.append(" t.vm_lastbuy_date >= :consumeDay ");
				sql.append(" AND buy_times < :loyalVipTimes");
				sql.append(" AND sh_money < :richVipMoney");
			}
			else if("5".equals(select_member_type)){
				sql.append(" (t.vm_lastbuy_date >= :lossDay AND t.vm_lastbuy_date <= :consumeDay)");
				sql.append(" AND buy_times >= :loyalVipTimes");
				sql.append(" AND sh_money>= :richVipMoney");
			}
			else if("6".equals(select_member_type)){
				sql.append(" (t.vm_lastbuy_date >= :lossDay AND t.vm_lastbuy_date <= :consumeDay)");
				sql.append(" AND buy_times >= :loyalVipTimes");
				sql.append(" AND sh_money < :richVipMoney");
			}
			else if("7".equals(select_member_type)){
				sql.append(" (t.vm_lastbuy_date >= :lossDay AND t.vm_lastbuy_date <= :consumeDay)");
				sql.append(" AND buy_times < :loyalVipTimes");
				sql.append(" AND sh_money>= :richVipMoney");
			}
			else if("8".equals(select_member_type)){
				sql.append(" (t.vm_lastbuy_date >= :lossDay AND t.vm_lastbuy_date <= :consumeDay)");
				sql.append(" AND buy_times < :loyalVipTimes");
				sql.append(" AND sh_money < :richVipMoney");
			}
			else if("9".equals(select_member_type)){
				sql.append(" t.vm_lastbuy_date IS NOT NULL AND t.vm_lastbuy_date < :lossDay");
				sql.append(" AND buy_times >= :loyalVipTimes");
				sql.append(" AND sh_money>= :richVipMoney");
			}
			else if("10".equals(select_member_type)){
				sql.append(" t.vm_lastbuy_date IS NOT NULL AND t.vm_lastbuy_date < :lossDay");
				sql.append(" AND buy_times >= :loyalVipTimes");
				sql.append(" AND sh_money < :richVipMoney");
			}
			else if("11".equals(select_member_type)){
				sql.append(" t.vm_lastbuy_date IS NOT NULL AND t.vm_lastbuy_date < :lossDay");
				sql.append(" AND buy_times < :loyalVipTimes");
				sql.append(" AND sh_money>= :richVipMoney");
			}
			else if("12".equals(select_member_type)){
				sql.append(" t.vm_lastbuy_date IS NOT NULL AND t.vm_lastbuy_date < :lossDay");
				sql.append(" AND buy_times < :loyalVipTimes");
				sql.append(" AND sh_money < :richVipMoney");
			}
			sql.append(")");
		}else {
			if (StringUtil.isNotEmpty(begindate)) {
				sql.append(" AND vm_date >= :begindate ");
			}
			if (StringUtil.isNotEmpty(enddate)) {
				sql.append(" AND vm_date <= :enddate ");
			}
			if (StringUtil.isNotEmpty(vm_mt_code)) {
				sql.append(" AND vm_mt_code = :vm_mt_code ");
			}
			if (StringUtil.isNotEmpty(vm_shop_code)) {
				sql.append(" AND vm_shop_code = :vm_shop_code ");
			}
			if (StringUtil.isNotEmpty(vm_manager_code)) {
				sql.append(" AND vm_manager_code = :vm_manager_code ");
			}
			if (StringUtil.isNotEmpty(vm_cardcode)) {
				sql.append(" AND INSTR(vm_cardcode, :vm_cardcode) > 0 ");
			}
			if (StringUtil.isNotEmpty(vm_name)) {
				sql.append(" AND INSTR(vm_name, :vm_name) > 0 ");
			}
			if (StringUtil.isNotEmpty(vm_mobile)) {
				sql.append(" AND (INSTR(vm_cardcode, :vm_mobile) > 0 OR INSTR(vm_mobile, :vm_mobile) > 0)");
			}
			if( StringUtil.isNotEmpty(begin_total_money) ){
				sql.append(" AND vm_total_money >= :begin_total_money");
			}
			if( StringUtil.isNotEmpty(end_total_money) ){
				sql.append(" AND vm_total_money <= :end_total_money");
			}
			if( StringUtil.isNotEmpty(begin_last_points) ){
				sql.append(" AND vm_points >= :begin_last_points");
			}
			if( StringUtil.isNotEmpty(end_last_points) ){
				sql.append(" AND vm_points <= :end_last_points");
			}
			if( StringUtil.isNotEmpty(begin_notbuy_day) && StringUtil.isNotEmpty(end_notbuy_day) ){
				Calendar cal = Calendar.getInstance();
				cal.add(Calendar.DATE, -Integer.parseInt(begin_notbuy_day));//近日期
				begin_notbuy_day = DateUtil.format(cal, "yyyy-MM-dd");
				Calendar calendar = Calendar.getInstance();
				calendar.add(Calendar.DATE, -Integer.parseInt(end_notbuy_day));//远日期
				end_notbuy_day = DateUtil.format(calendar, "yyyy-MM-dd");
				
				sql.append(" AND (");
				sql.append(" vm_lastbuy_date IS NOT NULL AND vm_lastbuy_date >= '"+end_notbuy_day+"' AND vm_lastbuy_date <= '"+begin_notbuy_day+"'");
				sql.append(" )");
			}
		}
		sql.append(") temp ");
		sql.append(" WHERE 1=1");
		if (StringUtil.isNotEmpty(vm_tag_name)) {
			sql.append(" AND INSTR(vm_tag_name, :vm_tag_name) > 0 ");
		}
		Integer count = namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
		return count;
	}

	@Override
	public List<T_Vip_Member> list(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object vm_mt_code = params.get("vm_mt_code");
		Object vm_shop_code = params.get("vm_shop_code");
		Object vm_manager_code = params.get("vm_manager_code");
		Object vm_cardcode = params.get("vm_cardcode");
		Object vm_name = params.get("vm_name");
		Object vm_mobile = params.get("vm_mobile");
		Object vm_tag_name = params.get("vm_tag_name");
		Object begin_total_money = params.get("begin_total_money");
		Object end_total_money = params.get("end_total_money");
		Object begin_last_points = params.get("begin_last_points");
		Object end_last_points = params.get("end_last_points");
		String begin_notbuy_day = (String)params.get("begin_notbuy_day");
		String end_notbuy_day = (String)params.get("end_notbuy_day");
		String select_member_type = (String)params.get("select_member_type");
		
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT * FROM (");
		sql.append("SELECT vm_id,vm_code,vm_mobile,vm_cardcode,vm_mt_code,vm_shop_code,vm_name,vm_password,vm_sex,");
		sql.append(" vm_birthday_type,vm_birthday,vm_lunar_birth,vm_state,vm_date,vm_enddate,vm_manager_code,t.companyid,");
		sql.append(" vm_points,vm_used_points,vm_init_points,vm_times,vm_total_money,vm_lastbuy_date,vm_lastbuy_money,");
		sql.append(" sp_name AS shop_name,IFNULL(datediff(now(),vm_lastbuy_date),'未消费') as vm_notbuy_day,");
		if(StringUtil.isNotEmpty(select_member_type)){
			sql.append("sh_money,buy_times,");
		}
		sql.append(" (SELECT mt_name FROM t_vip_membertype mt WHERE mt_code = vm_mt_code AND mt.companyid = t.companyid LIMIT 1) AS mt_name,");
		sql.append(" (SELECT em_name FROM t_base_emp em WHERE em_code = vm_manager_code AND em.companyid = t.companyid LIMIT 1) AS vm_manager,");
		sql.append(" (SELECT GROUP_CONCAT(vmt_vt_name) FROM t_vip_member_tag vmt WHERE vmt.vmt_vm_code = t.vm_code AND vmt.companyid = t.companyid GROUP BY vmt_vm_code) AS vm_tag_name ");
		sql.append(" FROM t_vip_member t");
		if(StringUtil.isNotEmpty(select_member_type)){
			sql.append(" LEFT JOIN (");
			sql.append(" SELECT sh_money,buy_times,sh_vip_code ");
			sql.append(" FROM");
			sql.append(" (SELECT IFNULL(SUM(sh_money),0) AS sh_money,SUM(CASE WHEN sh_state=0 THEN 1 WHEN sh_state=1 THEN -1 END )  AS buy_times,sh_vip_code");
			sql.append(" FROM t_sell_shop sh ");
			sql.append(" WHERE sh.sh_state = 0 ");
			sql.append(" AND LENGTH(sh.sh_vip_code)>0 ");
			//活跃客户
			if("1".equals(select_member_type)||"2".equals(select_member_type)||"3".equals(select_member_type)||"4".equals(select_member_type)){
				sql.append(" AND sh.sh_date >= :consumeDay ");
			}
			//休眠客户
			if("5".equals(select_member_type)||"6".equals(select_member_type)||"7".equals(select_member_type)||"8".equals(select_member_type)){
				sql.append(" AND sh.sh_date < :consumeDay AND sh_date >= :lossDay ");
			}
			//流失客户
			if("9".equals(select_member_type)||"10".equals(select_member_type)||"11".equals(select_member_type)||"12".equals(select_member_type)){
				sql.append(" AND sh.sh_date < :lossDay ");
			}
			sql.append(" AND sh.companyid = :companyid ");
			sql.append(" GROUP BY sh.sh_vip_code) as gradetab");
			sql.append(" ) as temp ON temp.sh_vip_code = t.vm_code ");
		}
		sql.append(" JOIN t_base_shop sp ON vm_shop_code = sp_code AND t.companyid = sp.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(" AND t.companyid=:companyid");
		if(StringUtil.isNotEmpty(select_member_type)){
			sql.append(" AND (");
			if("1".equals(select_member_type)){
				sql.append(" t.vm_lastbuy_date >= :consumeDay ");
				sql.append(" AND buy_times >= :loyalVipTimes");
				sql.append(" AND sh_money >= :richVipMoney");
			}
			else if("2".equals(select_member_type)){
				sql.append(" t.vm_lastbuy_date >= :consumeDay ");
				sql.append(" AND buy_times >= :loyalVipTimes");
				sql.append(" AND sh_money < :richVipMoney");
			}
			else if("3".equals(select_member_type)){
				sql.append(" t.vm_lastbuy_date >= :consumeDay ");
				sql.append(" AND buy_times < :loyalVipTimes");
				sql.append(" AND sh_money >= :richVipMoney");
			}
			else if("4".equals(select_member_type)){
				sql.append(" t.vm_lastbuy_date >= :consumeDay ");
				sql.append(" AND buy_times < :loyalVipTimes");
				sql.append(" AND sh_money < :richVipMoney");
			}
			else if("5".equals(select_member_type)){
				sql.append(" (t.vm_lastbuy_date >= :lossDay AND t.vm_lastbuy_date <= :consumeDay)");
				sql.append(" AND buy_times >= :loyalVipTimes");
				sql.append(" AND sh_money>= :richVipMoney");
			}
			else if("6".equals(select_member_type)){
				sql.append(" (t.vm_lastbuy_date >= :lossDay AND t.vm_lastbuy_date <= :consumeDay)");
				sql.append(" AND buy_times >= :loyalVipTimes");
				sql.append(" AND sh_money < :richVipMoney");
			}
			else if("7".equals(select_member_type)){
				sql.append(" (t.vm_lastbuy_date >= :lossDay AND t.vm_lastbuy_date <= :consumeDay)");
				sql.append(" AND buy_times < :loyalVipTimes");
				sql.append(" AND sh_money>= :richVipMoney");
			}
			else if("8".equals(select_member_type)){
				sql.append(" (t.vm_lastbuy_date >= :lossDay AND t.vm_lastbuy_date <= :consumeDay)");
				sql.append(" AND buy_times < :loyalVipTimes");
				sql.append(" AND sh_money < :richVipMoney");
			}
			else if("9".equals(select_member_type)){
				sql.append(" t.vm_lastbuy_date IS NOT NULL AND t.vm_lastbuy_date < :lossDay");
				sql.append(" AND buy_times >= :loyalVipTimes");
				sql.append(" AND sh_money>= :richVipMoney");
			}
			else if("10".equals(select_member_type)){
				sql.append(" t.vm_lastbuy_date IS NOT NULL AND t.vm_lastbuy_date < :lossDay");
				sql.append(" AND buy_times >= :loyalVipTimes");
				sql.append(" AND sh_money < :richVipMoney");
			}
			else if("11".equals(select_member_type)){
				sql.append(" t.vm_lastbuy_date IS NOT NULL AND t.vm_lastbuy_date < :lossDay");
				sql.append(" AND buy_times < :loyalVipTimes");
				sql.append(" AND sh_money>= :richVipMoney");
			}
			else if("12".equals(select_member_type)){
				sql.append(" t.vm_lastbuy_date IS NOT NULL AND t.vm_lastbuy_date < :lossDay");
				sql.append(" AND buy_times < :loyalVipTimes");
				sql.append(" AND sh_money < :richVipMoney");
			}
			sql.append(")");
		}else {
			if (StringUtil.isNotEmpty(begindate)) {
				sql.append(" AND vm_date >= :begindate ");
			}
			if (StringUtil.isNotEmpty(enddate)) {
				sql.append(" AND vm_date <= :enddate ");
			}
			if (StringUtil.isNotEmpty(vm_mt_code)) {
				sql.append(" AND vm_mt_code = :vm_mt_code ");
			}
			if (StringUtil.isNotEmpty(vm_shop_code)) {
				params.put("shop_codes", StringUtil.parseList(StringUtil.trimString(vm_shop_code)));
				sql.append(" AND vm_shop_code IN (:shop_codes) ");
			}
			if (StringUtil.isNotEmpty(vm_manager_code)) {
				sql.append(" AND vm_manager_code = :vm_manager_code ");
			}
			if (StringUtil.isNotEmpty(vm_cardcode)) {
				sql.append(" AND (INSTR(vm_cardcode, :vm_cardcode) > 0 OR INSTR(vm_mobile, :vm_cardcode) > 0 OR INSTR(vm_name, :vm_cardcode) > 0)");
			}
			if (StringUtil.isNotEmpty(vm_mobile)) {
				sql.append(" AND (INSTR(vm_cardcode, :vm_mobile) > 0 OR INSTR(vm_mobile, :vm_mobile) > 0)");
			}
			if (StringUtil.isNotEmpty(vm_name)) {
				sql.append(" AND INSTR(vm_name, :vm_name) > 0 ");
			}
			if( StringUtil.isNotEmpty(begin_total_money) ){
				sql.append(" AND vm_total_money >= :begin_total_money");
			}
			if( StringUtil.isNotEmpty(end_total_money) ){
				sql.append(" AND vm_total_money <= :end_total_money");
			}
			if( StringUtil.isNotEmpty(begin_last_points) ){
				sql.append(" AND vm_points >= :begin_last_points");
			}
			if( StringUtil.isNotEmpty(end_last_points) ){
				sql.append(" AND vm_points <= :end_last_points");
			}
			if( StringUtil.isNotEmpty(begin_notbuy_day) && StringUtil.isNotEmpty(end_notbuy_day) ){
				Calendar cal = Calendar.getInstance();
				cal.add(Calendar.DATE, -Integer.parseInt(begin_notbuy_day));//近日期
				begin_notbuy_day = DateUtil.format(cal, "yyyy-MM-dd");
				Calendar calendar = Calendar.getInstance();
				calendar.add(Calendar.DATE, -Integer.parseInt(end_notbuy_day));//远日期
				end_notbuy_day = DateUtil.format(calendar, "yyyy-MM-dd");
				
				sql.append(" AND (");
				sql.append(" vm_lastbuy_date IS NOT NULL AND vm_lastbuy_date >= '"+end_notbuy_day+"' AND vm_lastbuy_date <= '"+begin_notbuy_day+"'");
				sql.append(" )");
			}
		}
		sql.append(") temp ");
		sql.append(" WHERE 1=1 ");
		if (StringUtil.isNotEmpty(vm_tag_name)) {
			sql.append(" AND INSTR(vm_tag_name, :vm_tag_name) > 0 ");
		}
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY vm_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Vip_Member.class));
	}

	@Override
	public Integer birthday_count(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		String begindate = (String)params.get("begindate");
		String enddate = (String)params.get("enddate");
		Object vm_shop_code = params.get("vm_shop_code");
		Object vm_cardcode = params.get("vm_cardcode");
		Object vm_name = params.get("vm_name");
		Object vm_mobile = params.get("vm_mobile");
		Object isHadBirthday = params.get("isHadBirthday");
        String begin_notbuy_day = (String)params.get("begin_notbuy_day");
		String end_notbuy_day = (String)params.get("end_notbuy_day");
		
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1) ");
		sql.append(" FROM ");
		sql.append(" ( SELECT vm_id,vm_code,vm_cardcode,vm_name,vm_lastbuy_date,vm_times,vm_total_money,vm_birthday_type,vm_birthday,vm_lunar_birth,");
		sql.append("(CASE WHEN vm_birthday_type='0' THEN DATE_FORMAT(vm_birthday,'%m-%d') WHEN vm_birthday_type='1' THEN DATE_FORMAT(vm_lunar_birth,'%m-%d') END) AS birthday,");
		sql.append("vm_mobile,vm_points,vm_used_points,(vm_points-vm_used_points) AS vm_last_points,vm_date,IFNULL(sh_sell_money, 0) AS sh_sell_money,sp_name AS shop_name,");
		sql.append("(SELECT em_name FROM t_base_emp em WHERE em_code = vm_manager_code AND em.companyid = t.companyid LIMIT 1) AS vm_manager,");
		sql.append("(SELECT mt_name FROM t_vip_membertype mt WHERE mt_code = vm_mt_code AND mt.companyid = t.companyid LIMIT 1) AS mt_name ");
		sql.append(" FROM t_vip_member t ");
		sql.append(" JOIN t_base_shop sp ON vm_shop_code = sp_code AND t.companyid = sp.companyid");
		sql.append(" LEFT JOIN (SELECT sh_sell_money,sh_vip_code FROM (SELECT IFNULL(SUM(sh_sell_money),0) AS sh_sell_money,sh_vip_code,companyid FROM t_sell_shop ");
			sql.append(" WHERE sh_state IN ('0', '1') AND LENGTH(sh_vip_code) >0 AND DATE_FORMAT(sh_date, '%Y-%m-%d') >= :consumeDate AND companyid=:companyid GROUP BY sh_vip_code) AS gradetab) AS temp");
			sql.append(" on sh_vip_code = vm_code ");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if( StringUtil.isNotEmpty(vm_shop_code) ){
			sql.append(" AND vm_shop_code = :vm_shop_code");//店铺Code
		}
		if ( StringUtil.isNotEmpty(vm_name) ) {
			sql.append(" AND INSTR(vm_name, :vm_name) > 0 ");//会员姓名
		}
		if( StringUtil.isNotEmpty(vm_mobile) ){
			sql.append(" AND INSTR(vm_mobile, :vm_mobile) > 0 ");//手机号码
		}
		if( StringUtil.isNotEmpty(vm_cardcode) ){
			sql.append(" AND INSTR(vm_cardcode, :vm_cardcode) > 0 ");//会员卡号
		}
		sql.append(" AND (");
		if( StringUtil.isEmpty(isHadBirthday) ){
			
			String beginLunarDate = begindate;
			String endLunarDate = enddate;
			//公历转农历生日
			if( begindate.length()==10 ){
				beginLunarDate = CalendarUtil.getLunarCalendar(begindate);
				endLunarDate = CalendarUtil.getLunarCalendar(enddate);
				enddate = enddate.substring(5);
				begindate = begindate.substring(5);
				endLunarDate = endLunarDate.substring(5);
				beginLunarDate = beginLunarDate.substring(5);
			}
			
			if( enddate.compareTo(begindate)<0 ){//公历生日
				//跨年份生日
				sql.append(" (vm_birthday_type='0' AND (");
				sql.append(" (DATE_FORMAT(vm_birthday,'%m-%d')>='"+ begindate +"'");
				sql.append(" AND DATE_FORMAT(vm_birthday,'%m-%d')<='12-31')");
				sql.append(" OR (DATE_FORMAT(vm_birthday,'%m-%d')>='01-01'");
				sql.append(" AND DATE_FORMAT(vm_birthday,'%m-%d')<='"+ enddate +"') )");
				sql.append(" )");
			}else{
				sql.append(" (vm_birthday_type='0' AND DATE_FORMAT(vm_birthday,'%m-%d')>='"+ begindate +"'");
				sql.append(" AND DATE_FORMAT(vm_birthday,'%m-%d')<='"+ enddate +"')");
			}
			sql.append(" OR ");
			if( endLunarDate.compareTo(beginLunarDate)<0 ){//阴历生日
				//跨年份生日
				sql.append(" (vm_birthday_type='1' AND (");
				sql.append(" (DATE_FORMAT(vm_lunar_birth,'%m-%d')>='"+ beginLunarDate +"'");
				sql.append(" AND DATE_FORMAT(vm_lunar_birth,'%m-%d')<='12-31')");
				sql.append(" OR (DATE_FORMAT(vm_lunar_birth,'%m-%d')>='01-01'");
				sql.append(" AND DATE_FORMAT(vm_lunar_birth,'%m-%d')<='"+ endLunarDate +"') )");
				sql.append(" )");
			}else{
			    sql.append(" (vm_birthday_type='1' AND DATE_FORMAT(vm_lunar_birth,'%m-%d')>='"+ beginLunarDate +"'");
			    sql.append(" AND DATE_FORMAT(vm_lunar_birth,'%m-%d')<='"+ endLunarDate +"')");
			}
			
		}else{
			//阴历OR阳历
			sql.append(" vm_birthday_type=:isHadBirthday");
			if( "0".equals(isHadBirthday) ){
				sql.append(" AND DATE_FORMAT(vm_birthday,'%m-%d')>='"+ begindate +"'");
				sql.append(" AND DATE_FORMAT(vm_birthday,'%m-%d')<='"+ enddate +"'");
			}else{
				sql.append(" AND DATE_FORMAT(vm_lunar_birth,'%m-%d')>='"+ begindate +"'");
				sql.append(" AND DATE_FORMAT(vm_lunar_birth,'%m-%d')<='"+ enddate +"'");
			}
		}
	    sql.append(" )");
	    sql.append(" AND t.companyid = :companyid");
		sql.append(" ORDER BY birthday ASC");
		sql.append(") AS temp");
		sql.append(" WHERE 1 = 1 ");
		if( StringUtil.isNotEmpty(begin_notbuy_day) && StringUtil.isNotEmpty(end_notbuy_day) ){
			Calendar cal = Calendar.getInstance();
			cal.add(Calendar.DATE, -Integer.parseInt(begin_notbuy_day));//近日期
			begin_notbuy_day = DateUtil.format(cal, "yyyy-MM-dd");
			
			Calendar calendar = Calendar.getInstance();
			calendar.add(Calendar.DATE, -Integer.parseInt(end_notbuy_day));//远日期
			end_notbuy_day = DateUtil.format(calendar, "yyyy-MM-dd");
			
			sql.append(" AND (");
			sql.append(" (vm_lastbuy_date IS NOT NULL AND vm_lastbuy_date>='"+end_notbuy_day+" 00:00:00' AND vm_lastbuy_date<='"+begin_notbuy_day+" 23:59:59')");
			sql.append(" OR (vm_lastbuy_date IS NULL AND vm_date>='"+end_notbuy_day+"' AND vm_date<='"+begin_notbuy_day+"')");
			sql.append(" )");
		}
//		sql.append("GD_GradeName,");
//		
//		sql.append("(SELECT GD_GradeName FROM t_vip_grade WHERE GD_EI_Id = mi_ei_id AND GD_Lower <= IFNULL(SH_SellMonery, 0) AND GD_Upper > IFNULL(SH_SellMonery, 0) LIMIT 0,1) AS GD_GradeName ");
		Integer count = namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
		return count;
	}

	@Override
	public List<T_Vip_Member> birthday_list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		String begindate = (String)params.get("begindate");
		String enddate = (String)params.get("enddate");
		Object vm_shop_code = params.get("vm_shop_code");
		Object vm_cardcode = params.get("vm_cardcode");
		Object vm_name = params.get("vm_name");
		Object vm_mobile = params.get("vm_mobile");
		Object isHadBirthday = params.get("isHadBirthday");
        String begin_notbuy_day = (String)params.get("begin_notbuy_day");
		String end_notbuy_day = (String)params.get("end_notbuy_day");
		
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT vm_id,vm_code,vm_cardcode,vm_name,vm_times,vm_total_money,mt_name,vm_birthday_type,vm_lastbuy_date,");
		sql.append("birthday AS vm_birthday,vm_mobile,vm_points,vm_used_points,vm_last_points,vm_notbuy_day,vm_date,vm_manager,mt_name,sh_sell_money,shop_name,");
		sql.append("(SELECT gd_name from t_vip_grade gd WHERE gd.companyid=temp.companyid AND gd.gd_lower <= sh_sell_money AND gd.gd_upper >= sh_sell_money LIMIT 0,1) as gd_name ");
		sql.append(" FROM ");
		sql.append(" ( SELECT vm_id,vm_code,vm_cardcode,vm_name,IFNULL(datediff(now(),vm_lastbuy_date),'未消费') as vm_notbuy_day,vm_lastbuy_date,vm_times,vm_total_money,vm_birthday_type,vm_birthday,vm_lunar_birth,t.companyid,");
		sql.append("(CASE WHEN vm_birthday_type='0' THEN DATE_FORMAT(vm_birthday,'%m-%d') WHEN vm_birthday_type='1' THEN DATE_FORMAT(vm_lunar_birth,'%m-%d') END) AS birthday,");
		sql.append("vm_mobile,vm_points,vm_used_points,(vm_points-vm_used_points) AS vm_last_points,vm_date,IFNULL(sh_sell_money, 0) AS sh_sell_money,sp_name AS shop_name,");
		sql.append("(SELECT em_name FROM t_base_emp em WHERE em_code = vm_manager_code AND em.companyid = t.companyid LIMIT 1) AS vm_manager,");
		sql.append("(SELECT mt_name FROM t_vip_membertype mt WHERE mt_code = vm_mt_code AND mt.companyid = t.companyid LIMIT 1) AS mt_name ");
		sql.append(" FROM t_vip_member t ");
		sql.append(" JOIN t_base_shop sp ON vm_shop_code = sp_code AND t.companyid = sp.companyid");
		sql.append(" LEFT JOIN (SELECT sh_sell_money,sh_vip_code FROM (SELECT IFNULL(SUM(sh_sell_money),0) AS sh_sell_money,sh_vip_code,companyid FROM t_sell_shop ");
			sql.append(" WHERE sh_state IN ('0', '1') AND LENGTH(sh_vip_code) >0 AND DATE_FORMAT(sh_date, '%Y-%m-%d') >= :consumeDate AND companyid=:companyid GROUP BY sh_vip_code) AS gradetab) AS temp");
			sql.append(" on sh_vip_code = vm_code ");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if( StringUtil.isNotEmpty(vm_shop_code) ){
			sql.append(" AND vm_shop_code = :vm_shop_code");//店铺Code
		}
		if ( StringUtil.isNotEmpty(vm_name) ) {
			sql.append(" AND INSTR(vm_name, :vm_name) > 0 ");//会员姓名
		}
		if( StringUtil.isNotEmpty(vm_mobile) ){
			sql.append(" AND INSTR(vm_mobile, :vm_mobile) > 0 ");//手机号码
		}
		if( StringUtil.isNotEmpty(vm_cardcode) ){
			sql.append(" AND INSTR(vm_cardcode, :vm_cardcode) > 0 ");//会员卡号
		}
		sql.append(" AND (");
		if( StringUtil.isEmpty(isHadBirthday) ){
			
			String beginLunarDate = begindate;
			String endLunarDate = enddate;
			//公历转农历生日
			if( begindate.length()==10 ){
				beginLunarDate = CalendarUtil.getLunarCalendar(begindate);
				endLunarDate = CalendarUtil.getLunarCalendar(enddate);
				enddate = enddate.substring(5);
				begindate = begindate.substring(5);
				endLunarDate = endLunarDate.substring(5);
				beginLunarDate = beginLunarDate.substring(5);
			}
			
			if( enddate.compareTo(begindate)<0 ){//公历生日
				//跨年份生日
				sql.append(" (vm_birthday_type='0' AND (");
				sql.append(" (DATE_FORMAT(vm_birthday,'%m-%d')>='"+ begindate +"'");
				sql.append(" AND DATE_FORMAT(vm_birthday,'%m-%d')<='12-31')");
				sql.append(" OR (DATE_FORMAT(vm_birthday,'%m-%d')>='01-01'");
				sql.append(" AND DATE_FORMAT(vm_birthday,'%m-%d')<='"+ enddate +"') )");
				sql.append(" )");
			}else{
				sql.append(" (vm_birthday_type='0' AND DATE_FORMAT(vm_birthday,'%m-%d')>='"+ begindate +"'");
				sql.append(" AND DATE_FORMAT(vm_birthday,'%m-%d')<='"+ enddate +"')");
			}
			sql.append(" OR ");
			if( endLunarDate.compareTo(beginLunarDate)<0 ){//阴历生日
				//跨年份生日
				sql.append(" (vm_birthday_type='1' AND (");
				sql.append(" (DATE_FORMAT(vm_lunar_birth,'%m-%d')>='"+ beginLunarDate +"'");
				sql.append(" AND DATE_FORMAT(vm_lunar_birth,'%m-%d')<='12-31')");
				sql.append(" OR (DATE_FORMAT(vm_lunar_birth,'%m-%d')>='01-01'");
				sql.append(" AND DATE_FORMAT(vm_lunar_birth,'%m-%d')<='"+ endLunarDate +"') )");
				sql.append(" )");
			}else{
			    sql.append(" (vm_birthday_type='1' AND DATE_FORMAT(vm_lunar_birth,'%m-%d')>='"+ beginLunarDate +"'");
			    sql.append(" AND DATE_FORMAT(vm_lunar_birth,'%m-%d')<='"+ endLunarDate +"')");
			}
			
		}else{
			//阴历OR阳历
			sql.append(" vm_birthday_type=:isHadBirthday");
			if( "0".equals(isHadBirthday) ){
				sql.append(" AND DATE_FORMAT(vm_birthday,'%m-%d')>='"+ begindate +"'");
				sql.append(" AND DATE_FORMAT(vm_birthday,'%m-%d')<='"+ enddate +"'");
			}else{
				sql.append(" AND DATE_FORMAT(vm_lunar_birth,'%m-%d')>='"+ begindate +"'");
				sql.append(" AND DATE_FORMAT(vm_lunar_birth,'%m-%d')<='"+ enddate +"'");
			}
		}
	    sql.append(" )");
	    sql.append(" AND t.companyid = :companyid");
		sql.append(" ORDER BY birthday ASC");
		sql.append(") AS temp");
		sql.append(" WHERE 1 = 1 ");
		if( StringUtil.isNotEmpty(begin_notbuy_day) && StringUtil.isNotEmpty(end_notbuy_day) ){
			Calendar cal = Calendar.getInstance();
			cal.add(Calendar.DATE, -Integer.parseInt(begin_notbuy_day));//近日期
			begin_notbuy_day = DateUtil.format(cal, "yyyy-MM-dd");
			
			Calendar calendar = Calendar.getInstance();
			calendar.add(Calendar.DATE, -Integer.parseInt(end_notbuy_day));//远日期
			end_notbuy_day = DateUtil.format(calendar, "yyyy-MM-dd");
			
			sql.append(" AND (");
			sql.append(" (vm_lastbuy_date IS NOT NULL AND vm_lastbuy_date>='"+end_notbuy_day+" 00:00:00' AND vm_lastbuy_date<='"+begin_notbuy_day+" 23:59:59')");
			sql.append(" OR (vm_lastbuy_date IS NULL AND vm_date>='"+end_notbuy_day+"' AND vm_date<='"+begin_notbuy_day+"')");
			sql.append(" )");
		}
		
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY vm_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Vip_Member.class));
	}

	@Override
	public Map<String, Object> birthday_sum(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		String begindate = (String)params.get("begindate");
		String enddate = (String)params.get("enddate");
		Object vm_shop_code = params.get("vm_shop_code");
		Object vm_cardcode = params.get("vm_cardcode");
		Object vm_name = params.get("vm_name");
		Object vm_mobile = params.get("vm_mobile");
		Object isHadBirthday = params.get("isHadBirthday");
        String begin_notbuy_day = (String)params.get("begin_notbuy_day");
		String end_notbuy_day = (String)params.get("end_notbuy_day");
		
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ");
		sql.append(" SUM(sh_sell_money) as sh_sell_money,SUM(vm_total_money) as vm_total_money,SUM(vm_times) as vm_times,SUM(vm_points) as vm_points,SUM(vm_last_points) as vm_last_points ");
		sql.append(" FROM ");
		sql.append(" ( SELECT vm_id,vm_code,vm_cardcode,vm_name,IFNULL(datediff(now(),vm_lastbuy_date),'未消费') as vm_notbuy_day,vm_lastbuy_date,vm_times,vm_total_money,vm_birthday_type,vm_birthday,vm_lunar_birth,t.companyid,");
		sql.append("(CASE WHEN vm_birthday_type='0' THEN DATE_FORMAT(vm_birthday,'%m-%d') WHEN vm_birthday_type='1' THEN DATE_FORMAT(vm_lunar_birth,'%m-%d') END) AS birthday,");
		sql.append("vm_mobile,vm_points,vm_used_points,(vm_points-vm_used_points) AS vm_last_points,vm_date,IFNULL(sh_sell_money, 0) AS sh_sell_money,sp_name AS shop_name,");
		sql.append("(SELECT em_name FROM t_base_emp em WHERE em_code = vm_manager_code AND em.companyid = t.companyid LIMIT 1) AS vm_manager,");
		sql.append("(SELECT mt_name FROM t_vip_membertype mt WHERE mt_code = vm_mt_code AND mt.companyid = t.companyid LIMIT 1) AS mt_name ");
		sql.append(" FROM t_vip_member t ");
		sql.append(" JOIN t_base_shop sp ON vm_shop_code = sp_code AND t.companyid = sp.companyid");
		sql.append(" LEFT JOIN (SELECT sh_sell_money,sh_vip_code FROM (SELECT IFNULL(SUM(sh_sell_money),0) AS sh_sell_money,sh_vip_code,companyid FROM t_sell_shop ");
			sql.append(" WHERE sh_state IN ('0', '1') AND LENGTH(sh_vip_code) >0 AND DATE_FORMAT(sh_date, '%Y-%m-%d') >= :consumeDate AND companyid=:companyid GROUP BY sh_vip_code) AS gradetab) AS temp");
			sql.append(" on sh_vip_code = vm_code ");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if( StringUtil.isNotEmpty(vm_shop_code) ){
			sql.append(" AND vm_shop_code = :vm_shop_code");//店铺Code
		}
		if ( StringUtil.isNotEmpty(vm_name) ) {
			sql.append(" AND INSTR(vm_name, :vm_name) > 0 ");//会员姓名
		}
		if( StringUtil.isNotEmpty(vm_mobile) ){
			sql.append(" AND INSTR(vm_mobile, :vm_mobile) > 0 ");//手机号码
		}
		if( StringUtil.isNotEmpty(vm_cardcode) ){
			sql.append(" AND INSTR(vm_cardcode, :vm_cardcode) > 0 ");//会员卡号
		}
		sql.append(" AND (");
		if( StringUtil.isEmpty(isHadBirthday) ){
			
			String beginLunarDate = begindate;
			String endLunarDate = enddate;
			//公历转农历生日
			if( begindate.length()==10 ){
				beginLunarDate = CalendarUtil.getLunarCalendar(begindate);
				endLunarDate = CalendarUtil.getLunarCalendar(enddate);
				enddate = enddate.substring(5);
				begindate = begindate.substring(5);
				endLunarDate = endLunarDate.substring(5);
				beginLunarDate = beginLunarDate.substring(5);
			}
			
			if( enddate.compareTo(begindate)<0 ){//公历生日
				//跨年份生日
				sql.append(" (vm_birthday_type='0' AND (");
				sql.append(" (DATE_FORMAT(vm_birthday,'%m-%d')>='"+ begindate +"'");
				sql.append(" AND DATE_FORMAT(vm_birthday,'%m-%d')<='12-31')");
				sql.append(" OR (DATE_FORMAT(vm_birthday,'%m-%d')>='01-01'");
				sql.append(" AND DATE_FORMAT(vm_birthday,'%m-%d')<='"+ enddate +"') )");
				sql.append(" )");
			}else{
				sql.append(" (vm_birthday_type='0' AND DATE_FORMAT(vm_birthday,'%m-%d')>='"+ begindate +"'");
				sql.append(" AND DATE_FORMAT(vm_birthday,'%m-%d')<='"+ enddate +"')");
			}
			sql.append(" OR ");
			if( endLunarDate.compareTo(beginLunarDate)<0 ){//阴历生日
				//跨年份生日
				sql.append(" (vm_birthday_type='1' AND (");
				sql.append(" (DATE_FORMAT(vm_lunar_birth,'%m-%d')>='"+ beginLunarDate +"'");
				sql.append(" AND DATE_FORMAT(vm_lunar_birth,'%m-%d')<='12-31')");
				sql.append(" OR (DATE_FORMAT(vm_lunar_birth,'%m-%d')>='01-01'");
				sql.append(" AND DATE_FORMAT(vm_lunar_birth,'%m-%d')<='"+ endLunarDate +"') )");
				sql.append(" )");
			}else{
			    sql.append(" (vm_birthday_type='1' AND DATE_FORMAT(vm_lunar_birth,'%m-%d')>='"+ beginLunarDate +"'");
			    sql.append(" AND DATE_FORMAT(vm_lunar_birth,'%m-%d')<='"+ endLunarDate +"')");
			}
			
		}else{
			//阴历OR阳历
			sql.append(" vm_birthday_type=:isHadBirthday");
			if( "0".equals(isHadBirthday) ){
				sql.append(" AND DATE_FORMAT(vm_birthday,'%m-%d')>='"+ begindate +"'");
				sql.append(" AND DATE_FORMAT(vm_birthday,'%m-%d')<='"+ enddate +"'");
			}else{
				sql.append(" AND DATE_FORMAT(vm_lunar_birth,'%m-%d')>='"+ begindate +"'");
				sql.append(" AND DATE_FORMAT(vm_lunar_birth,'%m-%d')<='"+ enddate +"'");
			}
		}
	    sql.append(" )");
	    sql.append(" AND t.companyid = :companyid");
		sql.append(" ORDER BY birthday ASC");
		sql.append(") AS temp");
		sql.append(" WHERE 1 = 1 ");
		if( StringUtil.isNotEmpty(begin_notbuy_day) && StringUtil.isNotEmpty(end_notbuy_day) ){
			Calendar cal = Calendar.getInstance();
			cal.add(Calendar.DATE, -Integer.parseInt(begin_notbuy_day));//近日期
			begin_notbuy_day = DateUtil.format(cal, "yyyy-MM-dd");
			
			Calendar calendar = Calendar.getInstance();
			calendar.add(Calendar.DATE, -Integer.parseInt(end_notbuy_day));//远日期
			end_notbuy_day = DateUtil.format(calendar, "yyyy-MM-dd");
			
			sql.append(" AND (");
			sql.append(" (vm_lastbuy_date IS NOT NULL AND vm_lastbuy_date>='"+end_notbuy_day+" 00:00:00' AND vm_lastbuy_date<='"+begin_notbuy_day+" 23:59:59')");
			sql.append(" OR (vm_lastbuy_date IS NULL AND vm_date>='"+end_notbuy_day+"' AND vm_date<='"+begin_notbuy_day+"')");
			sql.append(" )");
		}
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}

	@Override
	public Integer countByShop(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object shop_uptype = params.get(CommonUtil.SHOP_UPTYPE);
		Object vm_shop_code = params.get("vm_shop_code");
		Object vm_cardcode = params.get("vm_cardcode");
		Object vm_name = params.get("vm_name");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_vip_member t");
		sql.append(" JOIN t_base_shop s");
		sql.append(" ON s.sp_code=t.vm_shop_code");
		sql.append(" AND s.companyid=t.companyid");
		if(CommonUtil.THREE.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)){ // 如果是自营店，则下上级店铺相同所有会员
			sql.append(getSellShopSQL(shop_uptype, 0));
		}else{
			sql.append(" AND sp_code=:shop_code");
		} 
		sql.append(" WHERE 1 = 1");
		if (StringUtil.isNotEmpty(vm_shop_code)) {
			sql.append(" AND vm_shop_code = :vm_shop_code ");
		}
		if (StringUtil.isNotEmpty(vm_cardcode)) {
			sql.append(" AND (INSTR(vm_cardcode, :vm_cardcode) > 0 OR INSTR(vm_mobile, :vm_cardcode) > 0)");
		}
		if (StringUtil.isNotEmpty(vm_name)) {
			sql.append(" AND INSTR(vm_name, :vm_name) > 0 ");
		}
		sql.append(" AND t.companyid=:companyid");
		Integer count = namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
		return count;
	}

	@Override
	public List<T_Vip_Member> listByShop(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object shop_uptype = params.get(CommonUtil.SHOP_UPTYPE);
		Object vm_shop_code = params.get("vm_shop_code");
		Object vm_cardcode = params.get("vm_cardcode");
		Object vm_name = params.get("vm_name");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT vm_id,vm_code,vm_mobile,vm_cardcode,vm_mt_code,vm_shop_code,vm_name,vm_sex,");
		sql.append(" vm_birthday_type,vm_birthday,vm_lunar_birth,vm_state,vm_date,vm_manager_code,");
		sql.append(" s.sp_name AS shop_name,");
		sql.append(" (SELECT mt_name FROM t_vip_membertype mt WHERE mt_code = vm_mt_code AND mt.companyid = t.companyid LIMIT 1) AS mt_name");
		sql.append(" FROM t_vip_member t");
		sql.append(" JOIN t_base_shop s");
		sql.append(" ON s.sp_code=t.vm_shop_code");
		sql.append(" AND s.companyid=t.companyid");
		if(CommonUtil.THREE.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)){ // 如果是自营店，则下上级店铺相同所有会员
			sql.append(getSellShopSQL(shop_uptype, 0));
		}else{
			sql.append(" AND sp_code=:shop_code");
		} 
		sql.append(" WHERE 1 = 1");
		if (StringUtil.isNotEmpty(vm_shop_code)) {
			sql.append(" AND vm_shop_code = :vm_shop_code ");
		}
		if (StringUtil.isNotEmpty(vm_cardcode)) {
			sql.append(" AND (INSTR(vm_cardcode, :vm_cardcode) > 0 OR INSTR(vm_mobile, :vm_cardcode) > 0)");
		}
		if (StringUtil.isNotEmpty(vm_name)) {
			sql.append(" AND INSTR(vm_name, :vm_name) > 0 ");
		}
		sql.append(" AND t.companyid=:companyid");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY vm_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Vip_Member.class));
	}

	@Override
	public Integer queryByCardCode(T_Vip_Member member, T_Sys_User user) {
		Map<String, Object> params = new HashMap<String, Object>();
		params.put(CommonUtil.COMPANYID, member.getCompanyid());
		params.put("vm_cardcode", member.getVm_cardcode());
		params.put("vm_id", member.getVm_id());
		
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT vm_id FROM t_vip_member m");
		sql.append(" JOIN t_base_shop s ON s.sp_code=m.vm_shop_code AND s.companyid=m.companyid");
		if (CommonUtil.ONE.equals(user.getShoptype()) || CommonUtil.TWO.equals(user.getShoptype())) {
			sql.append(getShopSQL(user.getShoptype(), 0));
			params.put(CommonUtil.SHOP_CODE, user.getUs_shop_code());
			params.put(CommonUtil.SHOP_TYPE, user.getShoptype());
		}else if (CommonUtil.THREE.equals(user.getShoptype()) || CommonUtil.FIVE.equals(user.getShoptype())) {
			sql.append(getShopSQL(user.getShoptype(), 0));
			params.put(CommonUtil.SHOP_CODE, user.getShop_upcode());
			params.put(CommonUtil.SHOP_TYPE, user.getShop_uptype());
		}else {
			sql.append(" AND sp_code = :shop_code");
			params.put(CommonUtil.SHOP_CODE, user.getUs_shop_code());
		}
		sql.append(" WHERE 1=1");
		if(null != member.getVm_cardcode() && !"".equals(member.getVm_cardcode())){
			sql.append(" AND (vm_cardcode=:vm_cardcode OR vm_mobile=:vm_cardcode)");
		}
		if(null != member.getVm_id() && member.getVm_id() > 0){
			sql.append(" AND vm_id <> :vm_id");
		}
		sql.append(" AND m.companyid=:companyid");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), params,Integer.class);
		}catch(Exception e){
			return null;
		}
	}
	
	@Override
	public Integer queryByMobile(T_Vip_Member member, T_Sys_User user) {
		Map<String, Object> params = new HashMap<String, Object>();
		params.put(CommonUtil.COMPANYID, member.getCompanyid());
		params.put("vm_mobile", member.getVm_mobile());
		params.put("vm_id", member.getVm_id());
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT vm_id FROM t_vip_member m");
		sql.append(" JOIN t_base_shop s ON s.sp_code=m.vm_shop_code AND s.companyid=m.companyid");
		if (CommonUtil.ONE.equals(user.getShoptype()) || CommonUtil.TWO.equals(user.getShoptype())) {
			sql.append(getShopSQL(user.getShoptype(), 0));
			params.put(CommonUtil.SHOP_CODE, user.getUs_shop_code());
			params.put(CommonUtil.SHOP_TYPE, user.getShoptype());
		}else if (CommonUtil.THREE.equals(user.getShoptype()) || CommonUtil.FIVE.equals(user.getShoptype())) {
			sql.append(getShopSQL(user.getShoptype(), 0));
			params.put(CommonUtil.SHOP_CODE, user.getShop_upcode());
			params.put(CommonUtil.SHOP_TYPE, user.getShop_uptype());
		}else {
			sql.append(" AND sp_code = :shop_code");
			params.put(CommonUtil.SHOP_CODE, user.getUs_shop_code());
		}
		sql.append(" WHERE 1=1");
		if(null != member.getVm_mobile() && !"".equals(member.getVm_mobile())){
			sql.append(" AND (vm_cardcode=:vm_mobile OR vm_mobile=:vm_mobile)");
		}
		if(null != member.getVm_id() && member.getVm_id() > 0){
			sql.append(" AND vm_id <> :vm_id");
		}
		sql.append(" AND m.companyid=:companyid");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), params,Integer.class);
		}catch(Exception e){
			return null;
		}
	}
	
	@Override
	public Integer queryByCardCode(T_Vip_Member member) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT vm_id FROM t_vip_member m");
		sql.append(" JOIN t_base_shop s");
		sql.append(" ON s.sp_code=m.vm_shop_code");
		sql.append(" AND s.companyid=m.companyid");
		if(CommonUtil.THREE.equals(member.getCashier().getShop_type())||CommonUtil.FIVE.equals(member.getCashier().getShop_type())){ // 如果登录的角色范围是自营店，则查询自营店下上级店铺相同所有自营店会员数据
			sql.append(getSellShopSQL(member.getCashier().getShop_uptype(), 0,member.getCashier().getShop_upcode()));
		}else{
			sql.append(" AND sp_code="+member.getCashier().getCa_shop_code());
		} 
		sql.append(" WHERE 1=1");
		if(null != member.getVm_cardcode() && !"".equals(member.getVm_cardcode())){
			sql.append(" AND (vm_cardcode=:vm_cardcode OR vm_mobile=:vm_cardcode)");
		}
		if(null != member.getVm_id() && member.getVm_id() > 0){
			sql.append(" AND vm_id <> :vm_id");
		}
		sql.append(" AND m.companyid=:companyid");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new BeanPropertySqlParameterSource(member),Integer.class);
		}catch(Exception e){
			return null;
		}
	}
	
	@Override
	public Integer queryByMobile(T_Vip_Member member) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT vm_id FROM t_vip_member m");
		sql.append(" JOIN t_base_shop s");
		sql.append(" ON s.sp_code=m.vm_shop_code");
		sql.append(" AND s.companyid=m.companyid");
		if(CommonUtil.THREE.equals(member.getCashier().getShop_type())||CommonUtil.FIVE.equals(member.getCashier().getShop_type())){ // 如果登录的角色范围是自营店，则查询自营店下上级店铺相同所有自营店会员数据
			sql.append(getSellShopSQL(member.getCashier().getShop_uptype(), 0,member.getCashier().getShop_upcode()));
		}else{
			sql.append(" AND sp_code="+member.getCashier().getCa_shop_code());
		} 
		sql.append(" WHERE 1=1");
		if(null != member.getVm_mobile() && !"".equals(member.getVm_mobile())){
			sql.append(" AND (vm_cardcode=:vm_mobile OR vm_mobile=:vm_mobile)");
		}
		if(null != member.getVm_id() && member.getVm_id() > 0){
			sql.append(" AND vm_id <> :vm_id");
		}
		sql.append(" AND m.companyid=:companyid");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new BeanPropertySqlParameterSource(member),Integer.class);
		}catch(Exception e){
			return null;
		}
	}
	
	
	@Override
	public T_Vip_Member vipById(Integer vm_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT vm_id,vm_code,vm_name,vm_mobile,vm_cardcode,vm_mt_code,vm_points,mt_point_to_money sh_sell_money");
		sql.append(" FROM t_vip_member t");
		sql.append(" JOIN t_vip_membertype m");
		sql.append(" ON m.mt_code=t.vm_mt_code");
		sql.append(" AND m.companyid=t.companyid");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND t.vm_id=:vm_id");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("vm_id", vm_id),new BeanPropertyRowMapper<>(T_Vip_Member.class));
		}catch(Exception e){
			return null;
		}
	}

	@Override
	public T_Vip_Member queryByID(Integer vm_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT vm_id,vm_code,vm_mobile,vm_cardcode,vm_mt_code,vm_shop_code,vm_name,vm_password,vm_sex,");
		sql.append(" vm_birthday_type,vm_birthday,vm_lunar_birth,vm_state,vm_date,vm_enddate,vm_manager_code,companyid,");
		sql.append(" vm_total_point,vm_points,vm_used_points,vm_init_points,vm_times,vm_total_money,vm_lastbuy_date,vm_lastbuy_money,vm_img_path,");
		sql.append(" (SELECT sp_name FROM t_base_shop sp WHERE sp_code = vm_shop_code AND sp.companyid = t.companyid LIMIT 1) AS shop_name,");
		sql.append(" (SELECT mt_name FROM t_vip_membertype mt WHERE mt_code = vm_mt_code AND mt.companyid = t.companyid LIMIT 1) AS mt_name,");
		sql.append(" (SELECT em_name FROM t_base_emp em WHERE em_code = vm_manager_code AND em.companyid = t.companyid LIMIT 1) AS vm_manager");
		sql.append(" FROM t_vip_member t");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND t.vm_id=:vm_id");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("vm_id", vm_id),new BeanPropertyRowMapper<>(T_Vip_Member.class));
		}catch(Exception e){
			return null;
		}
	}
	
	@Override
	public T_Vip_Member loadByCode(String vm_code,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT vm_id,vm_code,vm_mobile,vm_cardcode,vm_mt_code,vm_shop_code,vm_name,vm_password,vm_sex,");
		sql.append(" vm_birthday_type,vm_birthday,vm_lunar_birth,vm_state,vm_date,vm_enddate,vm_manager_code,companyid,");
		sql.append(" vm_total_point,vm_points,vm_used_points,vm_init_points,vm_times,vm_total_money,vm_lastbuy_date,vm_lastbuy_money,vm_img_path,");
		sql.append(" (SELECT sp_name FROM t_base_shop sp WHERE sp_code = vm_shop_code AND sp.companyid = t.companyid LIMIT 1) AS shop_name,");
		sql.append(" (SELECT mt_name FROM t_vip_membertype mt WHERE mt_code = vm_mt_code AND mt.companyid = t.companyid LIMIT 1) AS mt_name,");
		sql.append(" (SELECT em_name FROM t_base_emp em WHERE em_code = vm_manager_code AND em.companyid = t.companyid LIMIT 1) AS vm_manager");
		sql.append(" FROM t_vip_member t");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND t.vm_code=:vm_code");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("vm_code", vm_code).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Vip_Member.class));
		}catch(Exception e){
			return null;
		}
	}

	@Override
	public T_Vip_Member_Info queryInfoByCode(String vmi_code, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT vmi_id,vmi_code,vmi_anima,vmi_marriage_state,vmi_nation,vmi_bloodtype,vmi_idcard_type,vmi_idcard,vmi_job,vmi_qq,");
		sql.append(" vmi_wechat,vmi_email,vmi_address,vmi_work_unit,vmi_faith,vmi_family_festival,vmi_culture,vmi_family,vmi_oral_habit,");
		sql.append(" vmi_transport,vmi_like_topic,vmi_taboo_topic,vmi_smoke,vmi_like_food,vmi_income_level,vmi_character,vmi_hobby,vmi_height,");
		sql.append(" vmi_weight,vmi_skin,vmi_bust,vmi_waist,vmi_hips,vmi_foottype,vmi_size_top,vmi_size_lower,vmi_size_shoes,vmi_size_bra,");
		sql.append(" vmi_trousers_length,vmi_like_color,vmi_like_brand,vmi_wear_prefer,companyid");
		sql.append(" FROM t_vip_member_info");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND vmi_code=:vmi_code");
		sql.append(" AND companyid=:companyid");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("vmi_code", vmi_code).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Vip_Member_Info.class));
		}catch(Exception e){
			return null;
		}
	}
	
	@Override
	public Integer queryInfoIdByCode(String vmi_code, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT vmi_id");
		sql.append(" FROM t_vip_member_info");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND vmi_code=:vmi_code");
		sql.append(" AND companyid=:companyid");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("vmi_code", vmi_code).addValue("companyid", companyid),Integer.class);
		}catch(Exception e){
			return null;
		}
	}

	@Override
	public void save(T_Vip_Member member, T_Vip_Member_Info memberInfo) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT f_autoincre_code(max(vm_code+0)) FROM t_vip_member ");
		sql.append(" WHERE 1=1");
		sql.append(" AND companyid=:companyid");
		String code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(member), String.class);
		member.setVm_code(code);
		memberInfo.setVmi_code(code);
		member.setVm_sysdate(DateUtil.getCurrentTime());
		sql.setLength(0);
		sql.append("INSERT INTO t_vip_member");
		sql.append("(vm_code,vm_mobile,vm_cardcode,vm_mt_code,vm_shop_code,vm_name,vm_password,vm_sex,vm_sysdate,");
		sql.append(" vm_birthday_type,vm_birthday,vm_lunar_birth,vm_state,vm_date,vm_enddate,vm_manager_code,companyid,");
		sql.append(" vm_points,vm_used_points,vm_init_points,vm_times,vm_total_money,vm_lastbuy_date,vm_lastbuy_money,vm_img_path)");
		sql.append(" VALUES");
		sql.append("(:vm_code,:vm_mobile,:vm_cardcode,:vm_mt_code,:vm_shop_code,:vm_name,:vm_password,:vm_sex,:vm_sysdate,");
		sql.append(" :vm_birthday_type,:vm_birthday,:vm_lunar_birth,:vm_state,:vm_date,:vm_enddate,:vm_manager_code,:companyid,");
		sql.append(" :vm_points,:vm_used_points,:vm_init_points,:vm_times,:vm_total_money,:vm_lastbuy_date,:vm_lastbuy_money,:vm_img_path)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(member),holder);
		member.setVm_id(holder.getKey().intValue());
		sql.setLength(0);
		sql.append("INSERT INTO t_vip_member_info");
		sql.append(" (vmi_code,vmi_anima,vmi_marriage_state,vmi_nation,vmi_bloodtype,vmi_idcard_type,vmi_idcard,vmi_job,vmi_qq,");
		sql.append(" vmi_wechat,vmi_email,vmi_address,vmi_work_unit,vmi_faith,vmi_family_festival,vmi_culture,vmi_family,vmi_oral_habit,");
		sql.append(" vmi_transport,vmi_like_topic,vmi_taboo_topic,vmi_smoke,vmi_like_food,vmi_income_level,vmi_character,vmi_hobby,vmi_height,");
		sql.append(" vmi_weight,vmi_skin,vmi_bust,vmi_waist,vmi_hips,vmi_foottype,vmi_size_top,vmi_size_lower,vmi_size_shoes,vmi_size_bra,");
		sql.append(" vmi_trousers_length,vmi_like_color,vmi_like_brand,vmi_wear_prefer,companyid)");
		sql.append(" VALUES");
		sql.append(" (:vmi_code,:vmi_anima,:vmi_marriage_state,:vmi_nation,:vmi_bloodtype,:vmi_idcard_type,:vmi_idcard,:vmi_job,:vmi_qq,");
		sql.append(" :vmi_wechat,:vmi_email,:vmi_address,:vmi_work_unit,:vmi_faith,:vmi_family_festival,:vmi_culture,:vmi_family,:vmi_oral_habit,");
		sql.append(" :vmi_transport,:vmi_like_topic,:vmi_taboo_topic,:vmi_smoke,:vmi_like_food,:vmi_income_level,:vmi_character,:vmi_hobby,:vmi_height,");
		sql.append(" :vmi_weight,:vmi_skin,:vmi_bust,:vmi_waist,:vmi_hips,:vmi_foottype,:vmi_size_top,:vmi_size_lower,:vmi_size_shoes,:vmi_size_bra,");
		sql.append(" :vmi_trousers_length,:vmi_like_color,:vmi_like_brand,:vmi_wear_prefer,:companyid)");
		KeyHolder holderInfo = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(memberInfo),holderInfo);
		memberInfo.setVmi_id(holderInfo.getKey().intValue());
		
		//保存积分记录
		sql.setLength(0);
		sql.append("INSERT INTO t_vip_pointlist(");
		sql.append("vpl_vm_code,vpl_point,vpl_type,vpl_date,");
		sql.append("vpl_shop_name,vpl_manager,companyid");
		sql.append(") VALUES (");
		sql.append(":vm_code,:vm_init_points,1,:vm_date,");
		sql.append(":shop_name,:vm_manager,:companyid");
		sql.append(")");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(member));
	}

	@Override
	public void update(T_Vip_Member member, T_Vip_Member_Info memberInfo) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_vip_member");
		sql.append(" SET vm_mobile=:vm_mobile");
		sql.append(" ,vm_cardcode=:vm_cardcode");
		sql.append(" ,vm_mt_code=:vm_mt_code");
		sql.append(" ,vm_shop_code=:vm_shop_code");
		sql.append(" ,vm_name=:vm_name");
		sql.append(" ,vm_sex=:vm_sex");
		sql.append(" ,vm_birthday_type=:vm_birthday_type");
		sql.append(" ,vm_birthday=:vm_birthday");
		sql.append(" ,vm_lunar_birth=:vm_lunar_birth");
		sql.append(" ,vm_state=:vm_state");
		sql.append(" ,vm_date=:vm_date");
		sql.append(" ,vm_enddate=:vm_enddate");
		sql.append(" ,vm_manager_code=:vm_manager_code");
		sql.append(" WHERE vm_id=:vm_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(member));
		sql.setLength(0);
		sql.append(" UPDATE t_vip_member_info");
		sql.append(" SET vmi_anima=:vmi_anima");
		sql.append(" ,vmi_marriage_state=:vmi_marriage_state");
		sql.append(" ,vmi_nation=:vmi_nation");
		sql.append(" ,vmi_bloodtype=:vmi_bloodtype");
		sql.append(" ,vmi_idcard_type=:vmi_idcard_type");
		sql.append(" ,vmi_idcard=:vmi_idcard");
		sql.append(" ,vmi_job=:vmi_job");
		sql.append(" ,vmi_qq=:vmi_qq");
		sql.append(" ,vmi_wechat=:vmi_wechat");
		sql.append(" ,vmi_email=:vmi_email");
		sql.append(" ,vmi_address=:vmi_address");
		sql.append(" ,vmi_work_unit=:vmi_work_unit");
		sql.append(" ,vmi_faith=:vmi_faith");
		sql.append(" ,vmi_family_festival=:vmi_family_festival");
		sql.append(" ,vmi_culture=:vmi_culture");
		sql.append(" ,vmi_family=:vmi_family");
		sql.append(" ,vmi_oral_habit=:vmi_oral_habit");
		sql.append(" ,vmi_transport=:vmi_transport");
		sql.append(" ,vmi_like_topic=:vmi_like_topic");
		sql.append(" ,vmi_taboo_topic=:vmi_taboo_topic");
		sql.append(" ,vmi_smoke=:vmi_smoke");
		sql.append(" ,vmi_like_food=:vmi_like_food");
		sql.append(" ,vmi_income_level=:vmi_income_level");
		sql.append(" ,vmi_character=:vmi_character");
		sql.append(" ,vmi_hobby=:vmi_hobby");
		sql.append(" ,vmi_height=:vmi_height");
		sql.append(" ,vmi_weight=:vmi_weight");
		sql.append(" ,vmi_skin=:vmi_skin");
		sql.append(" ,vmi_bust=:vmi_bust");
		sql.append(" ,vmi_waist=:vmi_waist");
		sql.append(" ,vmi_hips=:vmi_hips");
		sql.append(" ,vmi_foottype=:vmi_foottype");
		sql.append(" ,vmi_size_top=:vmi_size_top");
		sql.append(" ,vmi_size_lower=:vmi_size_lower");
		sql.append(" ,vmi_size_shoes=:vmi_size_shoes");
		sql.append(" ,vmi_size_bra=:vmi_size_bra");
		sql.append(" ,vmi_trousers_length=:vmi_trousers_length");
		sql.append(" ,vmi_like_color=:vmi_like_color");
		sql.append(" ,vmi_like_brand=:vmi_like_brand");
		sql.append(" ,vmi_wear_prefer=:vmi_wear_prefer");
		sql.append(" WHERE vmi_id=:vmi_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(memberInfo));
	}
	
	@Override
	public void updateImg(T_Vip_Member member) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_vip_member");
		sql.append(" SET vm_img_path=:vm_img_path");
		sql.append(" WHERE vm_id=:vm_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(member));
	}
	
	@Override
	public void del(Integer vm_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_vip_member");
		sql.append(" WHERE vm_id=:vm_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("vm_id", vm_id));
	}
	
	@Override
	public void delInfo(Integer vmi_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_vip_member_info");
		sql.append(" WHERE vmi_id=:vmi_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("vmi_id", vmi_id));
	}

	@Override
	public Integer assign_count(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_vip_member t");
		sql.append(" JOIN t_base_shop sp ON vm_shop_code = sp_code AND t.companyid = sp.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(getWhereSqlOfAssign(params));
		Integer count = namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
		return count;
	}

	@Override
	public List<T_Vip_Member> assign_list(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT vm_id,vm_code,vm_mobile,vm_cardcode,vm_mt_code,vm_shop_code,vm_name,vm_password,vm_sex,");
		sql.append(" vm_birthday_type,vm_birthday,vm_lunar_birth,vm_state,vm_date,vm_enddate,vm_manager_code,t.companyid,");
		sql.append(" vm_points,vm_used_points,vm_init_points,vm_times,vm_total_money,vm_lastbuy_date,vm_lastbuy_money,");
		sql.append(" sp_name AS shop_name,(vm_points-vm_used_points) as vm_last_points,");
		sql.append(" (SELECT mt_name FROM t_vip_membertype mt WHERE mt_code = vm_mt_code AND mt.companyid = t.companyid LIMIT 1) AS mt_name,");
		sql.append(" (SELECT em_name FROM t_base_emp em WHERE em_code = vm_old_manager AND em.companyid = t.companyid LIMIT 1) AS vm_old_manager,");
		sql.append(" (SELECT em_name FROM t_base_emp em WHERE em_code = vm_manager_code AND em.companyid = t.companyid LIMIT 1) AS vm_manager");
		sql.append(" FROM t_vip_member t");
		sql.append(" JOIN t_base_shop sp ON vm_shop_code = sp_code AND t.companyid = sp.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(getWhereSqlOfAssign(params));
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY vm_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Vip_Member.class));
	}

	@Override
	public Map<String, Object> assign_sum(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT");
		sql.append(" IFNULL(SUM(vm_times), 0) AS vm_times,");
		sql.append(" IFNULL(SUM(vm_total_money), 0) AS vm_total_money,");
		sql.append(" IFNULL(SUM(vm_points), 0) AS vm_points,");
		sql.append(" IFNULL(SUM(vm_used_points), 0) AS vm_used_points,");
		sql.append(" IFNULL(SUM(vm_points-vm_used_points), 0) AS vm_last_points");
		sql.append(" FROM");
		sql.append(" t_vip_member t ");
		sql.append(" JOIN t_base_shop sp ON vm_shop_code = sp_code AND t.companyid = sp.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(getWhereSqlOfAssign(params));
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}
	
	
	@Override
	public void update_assign_emp(Map<String, Object> params) {
		String rowId = (String)params.get("rowIds");
		String code = (String)params.get("codes");
		String[] codeName;
		String[] codes = code.split(",");
		String[] rowIds = rowId.split(","); 
		List<T_Vip_Member> vip_Members = new ArrayList<T_Vip_Member>();
		StringBuffer sql = new StringBuffer();
		//1.单个会员
		if(rowIds.length==1 && codes.length==1){
			codeName = codes[0].split(":");
			sql.append("update t_vip_member set vm_old_manager=vm_manager_code where LENGTH(vm_old_manager)=0 and vm_id=:vm_id");
			namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("vm_id", rowIds[0]));
					
			sql.setLength(0);
			sql.append(" update t_vip_member");
			sql.append(" set vm_manager_code=:vm_manager_code,vm_shop_code=:vm_shop_code");
			sql.append(" where vm_id=:vm_id");
			namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("vm_manager_code", codeName[0])
					.addValue("vm_shop_code", codeName[2]).addValue("vm_id", rowIds[0]));
		}
		//2.多个会员分配给一个办卡人员
		else if(rowIds.length>1 && codes.length==1){
			codeName = codes[0].split(":");
			T_Vip_Member t_Vip_Member = null;
			for(int i=0; i<rowIds.length; i++){
				t_Vip_Member = new T_Vip_Member();
				t_Vip_Member.setVm_manager(codeName[1]);
				t_Vip_Member.setVm_manager_code(codeName[0]);
				t_Vip_Member.setVm_shop_code(codeName[2]);
				t_Vip_Member.setVm_id(Integer.parseInt(rowIds[i]));
				vip_Members.add(t_Vip_Member);
			}
			sql.setLength(0);
			sql.append("update t_vip_member set vm_old_manager=vm_manager_code where LENGTH(vm_old_manager)=0 and vm_id=:vm_id");
			namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(vip_Members.toArray()));
			
			sql.setLength(0);
			sql.append(" update t_vip_member");
			sql.append(" set vm_manager_code=:vm_manager_code,vm_shop_code=:vm_shop_code");
			sql.append(" where vm_id=:vm_id");
			namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(vip_Members.toArray()));
		}
		//3.多个会员平均分配给多个办卡人员
		else if(rowIds.length>1 && codes.length>1){
			int index = 0;
			int codeLength = codes.length;
			T_Vip_Member t_Vip_Member = null;
			for( int i=0; i<rowIds.length; i++ ){
				index = i%codeLength;
				codeName = codes[index].split(":");
				if(rowIds[i]!=null && StringUtil.isNotEmpty(rowIds[i])){
					t_Vip_Member = new T_Vip_Member();
					t_Vip_Member.setVm_manager(codeName[1]);
					t_Vip_Member.setVm_manager_code(codeName[0]);
					t_Vip_Member.setVm_shop_code(codeName[2]);
					t_Vip_Member.setVm_id(Integer.parseInt(rowIds[i]));
					vip_Members.add(t_Vip_Member);
				}
			}
			sql.setLength(0);
			sql.append("update t_vip_member set vm_old_manager=vm_manager_code where LENGTH(vm_old_manager)=0 and vm_id=:vm_id");
			namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(vip_Members.toArray()));
			
			sql.setLength(0);
			sql.append(" update t_vip_member");
			sql.append(" set vm_manager_code=:vm_manager_code,vm_shop_code=:vm_shop_code");
			sql.append(" where vm_id=:vm_id");
			namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(vip_Members.toArray()));
		}
	}

	/**
	 * 分配办卡人员where条件
	 */
	private String getWhereSqlOfAssign(Map<String, Object> params){
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object vm_mt_code = params.get("vm_mt_code");
		Object vm_shop_code = params.get("vm_shop_code");
		Object vm_manager_code = params.get("vm_manager_code");
		Object vm_cardcode = params.get("vm_cardcode");
		Object vm_name = params.get("vm_name");
		Object vm_mobile = params.get("vm_mobile");
		Object vm_empty_manager = params.get("vm_empty_manager");
		StringBuffer sqlWhere = new StringBuffer();
		sqlWhere.append(" AND vm_state = 0 ");
		if (StringUtil.isNotEmpty(begindate)) {
			sqlWhere.append(" AND vm_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sqlWhere.append(" AND vm_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(vm_mt_code)) {
			sqlWhere.append(" AND vm_mt_code = :vm_mt_code ");
		}
		if (StringUtil.isNotEmpty(vm_shop_code)) {
			sqlWhere.append(" AND vm_shop_code = :vm_shop_code ");
		}
		if (StringUtil.isNotEmpty(vm_manager_code)) {
			sqlWhere.append(" AND vm_manager_code = :vm_manager_code ");
		}
		if (StringUtil.isNotEmpty(vm_cardcode)) {
			sqlWhere.append(" AND INSTR(vm_cardcode, :vm_cardcode) > 0 ");
		}
		if (StringUtil.isNotEmpty(vm_name)) {
			sqlWhere.append(" AND INSTR(vm_name, :vm_name) > 0 ");
		}
		if (StringUtil.isNotEmpty(vm_mobile)) {
			sqlWhere.append(" AND INSTR(vm_mobile, :vm_mobile) > 0 ");
		}
		if(StringUtil.isNotEmpty(vm_empty_manager)){
			sqlWhere.append(" AND vm_manager_code=''" );
		}
		sqlWhere.append(" AND t.companyid=:companyid");
		return sqlWhere.toString();
	}
	
	@Override
	public void save_visit(T_Vip_Visit visit) {
		StringBuffer sql = new StringBuffer();
		sql.append("insert into t_vip_visit(");
		sql.append(" vi_vm_code,vi_vm_name,vi_consume_money,vi_consume_date,vi_visit_date,vi_manager,");
		sql.append(" vi_content,vi_satisfaction,vi_manager_code,vi_shop_code,vi_us_id,vi_sysdate,vi_type,");
		sql.append(" vi_is_arrive,vi_remark,vi_way,companyid,vi_date");
		sql.append(" )values( ");
		sql.append(" :vi_vm_code,:vi_vm_name,:vi_consume_money,:vi_consume_date,:vi_visit_date,:vi_manager,");
		sql.append(" :vi_content,:vi_satisfaction,:vi_manager_code,:vi_shop_code,:vi_us_id,:vi_sysdate,:vi_type,");
		sql.append(" :vi_is_arrive,:vi_remark,:vi_way,:companyid");
		if(StringUtil.isEmpty(visit.getVi_date())){
			sql.append(",null");
		}else {
			sql.append(",:vi_date");
		}
		sql.append(")");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(visit));
	}

	@Override
	public void save_specialfocus(T_Vip_Visit visit) {
		StringBuffer sql = new StringBuffer();
		sql.append("insert into t_vip_specialfocus(");
		sql.append("sf_vm_code,sf_vm_name,sf_vm_mobile,sf_content,sf_contact_date,");
		sql.append("sf_manager_code,sf_manager,sf_type,sf_shop_code,sf_sysdate,companyid ");
		sql.append(" )values( ");
		sql.append(":vi_vm_code,:vi_vm_name,:vi_vm_mobile,:vi_remark,:vi_date,");
		sql.append(":vi_manager_code,:vi_manager,:vi_type,:vi_shop_code,:vi_sysdate,:companyid ");
		sql.append(")");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(visit));
	}
	
	@Override
	public void save_reachshop(T_Vip_Visit visit) {
		StringBuffer sql = new StringBuffer();
		sql.append("insert into t_vip_reachshop(");
		sql.append("rs_vm_code,rs_vm_name,rs_vm_mobile,rs_content,rs_arrive_date,");
		sql.append("rs_manager_code,rs_manager,rs_type,rs_shop_code,rs_sysdate,companyid ");
		sql.append(" )values( ");
		sql.append(":vi_vm_code,:vi_vm_name,:vi_vm_mobile,:vi_remark,:vi_date,");
		sql.append(":vi_manager_code,:vi_manager,:vi_type,:vi_shop_code,:vi_sysdate,:companyid ");
		sql.append(")");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(visit));
	}

	@Override
	public Map<String, List> getCheckImportMember(Map<String, Object> param) {
		Object shop_type = param.get(CommonUtil.SHOP_TYPE);
		Map<String, List> checkImportMember = new HashMap<String, List>();
		StringBuffer sql = new StringBuffer("");
		//查询当前商家的会员类别
		List<T_Vip_MemberType> memberTypes = new ArrayList<T_Vip_MemberType>();
		sql.append(" SELECT mt_code,mt_name");
		sql.append(" FROM t_vip_membertype t");
		sql.append(" WHERE 1 = 1");
        sql.append(" AND mt_shop_upcode=:shop_code");
		sql.append(" AND t.companyid=:companyid");
		memberTypes = namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Vip_MemberType.class));
		checkImportMember.put("memberTypes", memberTypes);
		
		//查询当前商家的店铺
		List<T_Base_Shop> shops = new ArrayList<T_Base_Shop>();
		sql.setLength(0);
		sql.append(" SELECT sp_code,sp_name ");
		sql.append(" FROM t_base_shop t");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(" AND t.companyid=:companyid");
		shops = namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Shop.class));
		checkImportMember.put("shops", shops);
		
		//查询当前商家会员编号及手机号码
		List<T_Vip_Member> members = new ArrayList<T_Vip_Member>();
		sql.setLength(0);
		sql.append(" SELECT vm_mobile,vm_cardcode FROM t_vip_member m");
		sql.append(" JOIN t_base_shop s ON s.sp_code=m.vm_shop_code AND s.companyid=m.companyid");
		if (CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)) {
			sql.append(getShopSQL(shop_type, 0));
		}else if (CommonUtil.THREE.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)) {
			sql.append(getShopSQL(shop_type, 0));
		}else {
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(" WHERE 1=1");
		sql.append(" AND m.companyid=:companyid");
		members = namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Vip_Member.class));
		checkImportMember.put("members", members);
		
		//查询经办人
		List<T_Base_Emp> emps = new ArrayList<T_Base_Emp>();
		sql.setLength(0);
		sql.append(" select em_code,em_name from t_base_emp t ");
		sql.append(" JOIN t_base_shop sp");
		sql.append(" ON sp.sp_code = t.em_shop_code");
		sql.append(" AND sp.companyid = t.companyid");
		sql.append(" where 1 = 1");
        sql.append(" AND (em_login_shop=:shop_code OR em_shop_code=:shop_code)");
		sql.append(" AND t.companyid=:companyid");
		emps = namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Emp.class));
		checkImportMember.put("emps", emps);
		
		return checkImportMember;
	}

	@Override
	public String queryMaxVmcode(Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT f_autoincre_code(max(vm_code+0)) FROM t_vip_member ");
		sql.append(" WHERE 1=1");
		sql.append(" AND companyid=:companyid");
		String code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("companyid", companyid), String.class);
		return code;
	}

	@Override
	public void saveMemberImport(List<T_Vip_Member> members) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_vip_member");
		sql.append("(vm_code,vm_mobile,vm_cardcode,vm_mt_code,vm_shop_code,vm_name,vm_sex,vm_password,vm_birthday_type,");
		sql.append(" vm_birthday,vm_lunar_birth,vm_state,vm_date,vm_enddate,vm_manager_code,vm_total_point,vm_points,vm_used_points,");
		sql.append(" vm_init_points,vm_times,vm_total_money,vm_lastbuy_date,vm_lastbuy_money,vm_sysdate,vm_img_path,companyid)");
		sql.append(" VALUES");
		sql.append("(:vm_code,:vm_mobile,:vm_cardcode,:vm_mt_code,:vm_shop_code,:vm_name,:vm_sex,:vm_password,:vm_birthday_type,");
		sql.append(" :vm_birthday,:vm_lunar_birth,:vm_state,:vm_date,:vm_enddate,:vm_manager_code,:vm_total_point,:vm_points,:vm_used_points,");
		sql.append(" :vm_init_points,:vm_times,:vm_total_money,:vm_lastbuy_date,:vm_lastbuy_money,:vm_sysdate,:vm_img_path,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(members.toArray()));
	}

	@Override
	public void saveMemberInfoImport(List<T_Vip_Member_Info> memberInfos) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_vip_member_info");
		sql.append(" (vmi_code,vmi_anima,vmi_marriage_state,vmi_nation,vmi_bloodtype,vmi_idcard_type,vmi_idcard,vmi_job,vmi_qq,");
		sql.append(" vmi_wechat,vmi_email,vmi_address,vmi_work_unit,vmi_faith,vmi_family_festival,vmi_culture,vmi_family,vmi_oral_habit,");
		sql.append(" vmi_transport,vmi_like_topic,vmi_taboo_topic,vmi_smoke,vmi_like_food,vmi_income_level,vmi_character,vmi_hobby,vmi_height,");
		sql.append(" vmi_weight,vmi_skin,vmi_bust,vmi_waist,vmi_hips,vmi_foottype,vmi_size_top,vmi_size_lower,vmi_size_shoes,vmi_size_bra,");
		sql.append(" vmi_trousers_length,vmi_like_color,vmi_like_brand,vmi_wear_prefer,companyid)");
		sql.append(" VALUES");
		sql.append(" (:vmi_code,:vmi_anima,:vmi_marriage_state,:vmi_nation,:vmi_bloodtype,:vmi_idcard_type,:vmi_idcard,:vmi_job,:vmi_qq,");
		sql.append(" :vmi_wechat,:vmi_email,:vmi_address,:vmi_work_unit,:vmi_faith,:vmi_family_festival,:vmi_culture,:vmi_family,:vmi_oral_habit,");
		sql.append(" :vmi_transport,:vmi_like_topic,:vmi_taboo_topic,:vmi_smoke,:vmi_like_food,:vmi_income_level,:vmi_character,:vmi_hobby,:vmi_height,");
		sql.append(" :vmi_weight,:vmi_skin,:vmi_bust,:vmi_waist,:vmi_hips,:vmi_foottype,:vmi_size_top,:vmi_size_lower,:vmi_size_shoes,:vmi_size_bra,");
		sql.append(" :vmi_trousers_length,:vmi_like_color,:vmi_like_brand,:vmi_wear_prefer,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(memberInfos.toArray()));
	}

	@Override
	public Map<String, Object> sell_count(Map<String, Object> paramMap) {
		Object shop_type = paramMap.get(CommonUtil.SHOP_TYPE);
		Object begindate = paramMap.get("begindate");
		Object vm_code = paramMap.get("vm_code");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT COUNT(1) count,");
		sql.append(" IFNULL(SUM(shl_amount), 0) AS shl_amount,");
		sql.append(" IFNULL(SUM(shl_money), 0) AS shl_money");
		sql.append(" FROM t_sell_shoplist shl");
		sql.append(" JOIN t_base_shop sp ");
		sql.append(" ON sp.sp_code=shl.shl_shop_code AND sp.companyid=shl.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND shl_date BETWEEN :begindate AND :enddate");
		}
		if(vm_code != null && !vm_code.equals("")){
			sql.append(" AND shl_vip_code=:vm_code");
		}
		sql.append(" AND shl.companyid = :companyid");
		Map<String,Object> map = namedParameterJdbcTemplate.queryForMap(sql.toString(), paramMap);
		return map;
	}

	@Override
	public List<T_Vip_ConsumeDetailList> sell_list(Map<String, Object> paramMap) {
		Object shop_type = paramMap.get(CommonUtil.SHOP_TYPE);
		Object begindate = paramMap.get("begindate");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ");
		sql.append(" shl_id as id,shl_number,pd_no,pd_name,shl_sell_price,shl_price,shl_money,shl_amount,");
		sql.append(" shl_cr_code,shl_sz_code,shl_br_code,shl_date,pd_unit,shl_vip_code,vm_cardcode,vm_mobile,vm_name,t.sp_name,");
		sql.append(" (shl_price/shl_sell_price) as shl_discount,");
		sql.append(" (SELECT pdm_img_path FROM t_base_product_img i WHERE i.pdm_pd_code=pd.pd_code AND i.companyid=pd.companyid LIMIT 1) pd_img_path,");
		sql.append(" (SELECT cr_name FROM t_base_color cr  WHERE cr.cr_code=t.shl_cr_code AND cr.companyid=t.companyid LIMIT 1 ) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz.sz_code=t.shl_sz_code AND sz.companyid=t.companyid LIMIT 1 ) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br.br_code=t.shl_br_code AND br.companyid=t.companyid LIMIT 1 ) AS br_name");
		sql.append(" FROM ");
		sql.append(" (SELECT shl.shl_id,shl.shl_number,shl.shl_pd_code,shl.shl_sell_price,shl.shl_price,shl.shl_money,shl.shl_amount,shl.shl_cr_code,");
		sql.append(" shl.shl_sz_code,shl.shl_br_code,shl.shl_vip_code,shl.shl_sysdate shl_date,shl.companyid,sp.sp_name,vm.vm_name,vm.vm_cardcode,vm.vm_mobile ");
		sql.append(" FROM t_sell_shoplist shl USE INDEX(IDX_SHL_CO_SP_DA)");
		sql.append(" JOIN t_vip_member vm ");
		sql.append(" ON vm.vm_code=shl.shl_vip_code AND vm.companyid=shl.companyid");
		sql.append(" JOIN t_base_shop sp ");
		sql.append(" ON sp.sp_code=shl.shl_shop_code AND sp.companyid=shl.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND shl_date BETWEEN :begindate AND :enddate ");
		}
		sql.append(" AND shl_vip_code=:vm_code");
		sql.append(" AND shl.companyid = :companyid");
		sql.append(" ORDER BY shl_id DESC");
		sql.append(" LIMIT :start,:end");
		sql.append(" ) t");
		sql.append(" JOIN t_base_product pd ");
		sql.append(" ON pd.pd_code=t.shl_pd_code AND pd.companyid=t.companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
					new BeanPropertyRowMapper<>(T_Vip_ConsumeDetailList.class));
	}
	@Override
	public List<T_Sell_Shop> shopList(Map<String, Object> paramMap) {
		Object begindate = paramMap.get("begindate");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT sh_sysdate,sh_money");
		sql.append(" FROM t_sell_shop t");
		sql.append(" WHERE 1=1");
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND sh_date BETWEEN :begindate AND :enddate ");
		}
		sql.append(" AND t.sh_vip_code=:vm_code");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" ORDER BY sh_sysdate");
		return namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
				new BeanPropertyRowMapper<>(T_Sell_Shop.class));
	}
	@Override
	public List<T_Sell_Ecoupon_User> ecouponList(Map<String, Object> paramMap) {
		Object shop_type = paramMap.get(CommonUtil.SHOP_TYPE);//登录人的类型ID
		Object ecu_state = paramMap.get("ecu_state");
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ecu_id,ecu_date,ecu_name,ecu_tel,ecu_vip_code,");
		sql.append(" ecu_money,ecu_limitmoney,ecu_enddate,ecu_state,");
		sql.append(" ecu_use_number,ecu_use_date,ecu_use_type");
		sql.append(" FROM t_sell_ecoupon_user t");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(" JOIN t_base_shop sp ON sp_code = ecu_shop_code AND sp.companyid = t.companyid");
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//加盟店
			sql.append(" WHERE 1 = 1");
			sql.append(" AND ecu_shop_code = :shop_code");
		}
		if(StringUtil.isNotEmpty(ecu_state)){
			sql.append(" AND ecu_state=:ecu_state");
		}
		sql.append(" AND ecu_vip_code=:vm_code");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" ORDER BY ecu_id DESC");
		sql.append(" LIMIT 50");
		return namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
				new BeanPropertyRowMapper<>(T_Sell_Ecoupon_User.class));
	}

	@Override
	public List<T_Vip_TryList> tryList(Map<String, Object> paramMap) {
		Object shop_type = paramMap.get(CommonUtil.SHOP_TYPE);
		Object begindate = paramMap.get("begindate");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT tr_id,pd_no,pd_name,tr_sysdate,tr_sell_price,");
		sql.append(" (SELECT cr_name FROM t_base_color c WHERE c.cr_code=temp.tr_cr_code AND c.companyid=temp.companyid) cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size c WHERE c.sz_code=temp.tr_sz_code AND c.companyid=temp.companyid) sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra c WHERE c.br_code=temp.tr_br_code AND c.companyid=temp.companyid) br_name");
		sql.append(" FROM (");
		sql.append(" SELECT tr_id,tr_sysdate,tr_pd_code,tr_cr_code,tr_sz_code,tr_br_code,tr_sell_price,tr_vm_code,t.companyid");
		sql.append(" FROM t_vip_trylist t");
		sql.append(" JOIN t_base_shop s");
		sql.append(" ON s.sp_code=t.tr_shop_code");
		sql.append(" AND s.companyid=t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND tr_sysdate BETWEEN :begindate AND :enddate");
		}
		sql.append(" AND tr_vm_code=:vm_code");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" LIMIT 50) temp");
		sql.append(" JOIN t_base_product p");
		sql.append(" ON p.pd_code=temp.tr_pd_code");
		sql.append(" AND p.companyid=temp.companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), paramMap, 
				new BeanPropertyRowMapper<>(T_Vip_TryList.class));
	}

	@Override
	public List<T_Vip_Visit> backList(Map<String, Object> paramMap) {
		Object shop_type = paramMap.get(CommonUtil.SHOP_TYPE);
		Object begindate = paramMap.get("begindate");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT vi_id,vi_vm_code,vi_visit_date,vi_manager,vi_content,vi_type,vi_way,vi_is_arrive,vi_remark,");
		sql.append("IF(vi_is_arrive=1, vi_date, '') AS vi_next_date,vi_date ");
		sql.append(" FROM t_vip_visit t ");
		sql.append(" JOIN t_base_shop sp ON t.vi_shop_code=sp.sp_code AND t.companyid=sp.companyid ");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if(StringUtil.isNotEmpty(begindate)){
			sql.append(" AND vi_visit_date BETWEEN :begindate AND :enddate");
		}
		sql.append(" AND vi_vm_code = :vm_code");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" ORDER BY vi_id DESC");
		sql.append(" LIMIT 30");
		return namedParameterJdbcTemplate.query(sql.toString(), paramMap, new BeanPropertyRowMapper<>(T_Vip_Visit.class));
	}

	@Override
	public List<NumberForm> timeList(Map<String, Object> paramMap) {
		Object begindate = paramMap.get("begindate");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT HOUR(sh_sysdate) code,SUM(sh_money) number");
		sql.append(" FROM t_sell_shop t");
		sql.append(" WHERE 1=1");
		if(StringUtil.isNotEmpty(begindate)){
			sql.append(" AND sh_date BETWEEN :begindate AND :enddate");
		}
		sql.append(" AND sh_vip_code = :vm_code");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" GROUP BY code");

		return namedParameterJdbcTemplate.query(sql.toString(), paramMap, new BeanPropertyRowMapper<>(NumberForm.class));
	}

	@Override
	public List<T_Base_Product> pushList(Map<String, Object> paramMap) {
		String oftenBrand = (String)paramMap.get("oftenBrand");
		String oftenType = (String)paramMap.get("oftenType");
		String oftenColor = (String)paramMap.get("oftenColor");
		String deport = (String)paramMap.get("deport");
		
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pd_code,pd_no,pd_name,pd_sell_price,sd_amount,");
		sql.append(" (select tp_name from t_base_type tp where tp.tp_code=pd.pd_tp_code and tp.companyid=pd.companyid LIMIT 1) as pd_tp_name,");//类别
		sql.append(" (select bd_name from t_base_brand bd where bd.bd_code=pd.pd_bd_code and bd.companyid=pd.companyid LIMIT 1) as pd_bd_name,");//品牌
		sql.append(" IFNULL((SELECT pdm_img_path FROM t_base_product_img pdm WHERE pdm.pdm_pd_code = pd.pd_code AND pdm.companyid = pd.companyid AND (pdm.pdm_cr_code = '' OR pdm.pdm_cr_code IN ("+oftenColor+")) LIMIT 1),'') AS pdm_img_path ");
		sql.append(" FROM t_stock_data t ");
		sql.append(" JOIN t_base_product pd ON pd.pd_code = t.sd_pd_code AND pd.companyid = t.companyid ");
		sql.append(" WHERE 1=1 ");
		if(StringUtil.isNotEmpty(oftenBrand)){
			sql.append(" AND pd.pd_bd_code IN ("+oftenBrand+") ");
		}
		if(StringUtil.isNotEmpty(oftenType)){
			sql.append(" AND pd.pd_tp_code IN ("+oftenType+") ");
		}
		if(StringUtil.isNotEmpty(oftenColor)){
			sql.append(" AND t.sd_cr_code IN ("+oftenColor+") ");
		}
		sql.append(" AND pd_sell_price < :maxPrice");
		sql.append(" AND t.sd_amount > 0 ");
		sql.append(" AND t.sd_dp_code IN ("+deport+") ");
		sql.append(" AND t.companyid = :companyid ");
		sql.append(" GROUP BY sd_pd_code ");
		sql.append(" ORDER BY pd_sell_price DESC ");
		sql.append(" LIMIT 5 ");
		return namedParameterJdbcTemplate.query(sql.toString(), paramMap, new BeanPropertyRowMapper<>(T_Base_Product.class));
	}

	@Override
	public List<T_Vip_Tag> query_vip_tag(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object vm_code = params.get("vm_code");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT vt_id,vt_code,vt_name,vt_manager_code,vt_shop_code,vt_sysdate,");
		sql.append(" (SELECT em_name FROM t_base_emp em WHERE em_code = vt_manager_code AND em.companyid = t.companyid LIMIT 1) AS vt_manager_name,");
		if(StringUtil.isNotEmpty(vm_code)){
			sql.append(" (");
			sql.append(" SELECT COUNT(1) FROM t_vip_member_tag vmt WHERE 1=1 ");
			sql.append(" AND vmt.companyid = :companyid ");
			sql.append(" AND vmt.vmt_vt_code = t.vt_code ");
			sql.append(" AND vmt.vmt_vm_code = :vm_code ");
			sql.append(" ) AS vip_member_used,");
		}
		sql.append(" (");
		sql.append(" SELECT COUNT(1) FROM t_vip_member_tag vmt WHERE 1=1 ");
		sql.append(" AND vmt.companyid = :companyid ");
		sql.append(" AND vmt.vmt_vt_code = t.vt_code ");
		sql.append(" ) AS vip_used_count ");
		sql.append(" FROM t_vip_tag t");
		sql.append(" JOIN t_base_shop sp ON t.vt_shop_code = sp.sp_code AND t.companyid = sp.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_upcode");
		}
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Vip_Tag.class));
	}

	@Override
	public void del_vip_tag(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_vip_tag");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND companyid=:companyid ");
		sql.append(" AND vt_code=:vt_code");
		namedParameterJdbcTemplate.update(sql.toString(), params);
	}

	@Override
	public void del_member_tag(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_vip_member_tag");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND companyid=:companyid ");
		sql.append(" AND vmt_vt_code=:vt_code");
		sql.append(" AND vmt_vm_code=:vm_code");
		namedParameterJdbcTemplate.update(sql.toString(), params);
	}

	@Override
	public void set_member_tag(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_vip_member_tag ");
		sql.append("(vmt_vm_code,vmt_vt_code,vmt_vt_name,companyid)");
		sql.append(" VALUES");
		sql.append("(:vm_code,:vt_code,:vt_name,:companyid)");
		namedParameterJdbcTemplate.update(sql.toString(), params);
	}

	@Override
	public void save_vip_tag(T_Vip_Tag t_Vip_Tag) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT f_autoincre_code(max(vt_code+0)) FROM t_vip_tag ");
		sql.append(" WHERE 1=1");
		sql.append(" AND companyid=:companyid");
		String code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(t_Vip_Tag), String.class);
		t_Vip_Tag.setVt_code(code);
		sql.setLength(0);
		sql.append("INSERT INTO t_vip_tag");
		sql.append("(vt_code,vt_name,vt_manager_code,vt_shop_code,vt_sysdate,companyid)");
		sql.append(" VALUES");
		sql.append("(:vt_code,:vt_name,:vt_manager_code,:vt_shop_code,:vt_sysdate,:companyid)");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(t_Vip_Tag));
		
		sql.setLength(0);
		sql.append("INSERT INTO t_vip_member_tag");
		sql.append("(vmt_vm_code,vmt_vt_code,companyid)");
		sql.append(" VALUES");
		sql.append("(:vm_code,:vt_code,:companyid)");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(t_Vip_Tag));
	}

	@Override
	public List<T_Base_Brand> queryVipOftenBrand(Map<String, Object> paramMap) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pd_bd_code AS bd_code,count(1) totalCount,");
		sql.append(" (SELECT bd_name FROM t_base_brand bd where bd.bd_code=pd.pd_bd_code and bd.companyid=pd.companyid LIMIT 1) as bd_name ");
		sql.append(" FROM t_sell_shoplist t ");
		sql.append(" JOIN t_base_product pd ON t.shl_pd_code = pd.pd_code AND t.companyid = pd.companyid ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND t.shl_vip_code=:vm_code ");
		sql.append(" AND t.companyid=:companyid ");
		sql.append(" GROUP BY pd_bd_code ORDER BY count(1) desc limit 3 ");
		return namedParameterJdbcTemplate.query(sql.toString(), paramMap, new BeanPropertyRowMapper<>(T_Base_Brand.class));
	}

	@Override
	public List<T_Base_Type> queryVipOftenType(Map<String, Object> paramMap) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pd_tp_code AS tp_code,count(1) totalCount,");
		sql.append(" (SELECT tp_name FROM t_base_type tp where tp.tp_code=pd.pd_tp_code and tp.companyid=pd.companyid LIMIT 1) as tp_name ");
		sql.append(" FROM t_sell_shoplist t ");
		sql.append(" JOIN t_base_product pd ON t.shl_pd_code = pd.pd_code AND t.companyid = pd.companyid ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND t.shl_vip_code=:vm_code ");
		sql.append(" AND t.companyid=:companyid ");
		sql.append(" GROUP BY pd_tp_code ORDER BY count(1) desc limit 3 ");
		return namedParameterJdbcTemplate.query(sql.toString(), paramMap, new BeanPropertyRowMapper<>(T_Base_Type.class));
	}

	@Override
	public List<T_Base_Color> queryVipOftenColor(Map<String, Object> paramMap) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT shl_cr_code AS cr_code,count(1) totalCount,");
		sql.append(" (SELECT cr_name FROM t_base_color cr where cr.cr_code=t.shl_cr_code and cr.companyid=t.companyid LIMIT 1) as cr_name ");
		sql.append(" FROM t_sell_shoplist t ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND t.shl_vip_code=:vm_code ");
		sql.append(" AND t.companyid=:companyid ");
		sql.append(" GROUP BY shl_cr_code ORDER BY count(1) desc limit 3 ");
		return namedParameterJdbcTemplate.query(sql.toString(), paramMap, new BeanPropertyRowMapper<>(T_Base_Color.class));
	}

	@Override
	public Map<String, Object> queryVipOftenPrice(Map<String, Object> paramMap) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT IFNULL(MAX(shl_price),0) maxPrice,IFNULL(MIN(shl_price),0) minPrice ");
		sql.append(" FROM t_sell_shoplist t ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND t.shl_vip_code = :vm_code ");
		sql.append(" AND t.companyid = :companyid ");
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), paramMap);
	}

	//---------------------------------前台---------------------------------------
	@Override
	public T_Vip_Member vipByCode(Map<String, Object> param) {
		Object shop_type = param.get(CommonUtil.SHOP_TYPE);
		Object shop_uptype = param.get(CommonUtil.SHOP_UPTYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT vm_id,vm_code,vm_mobile,vm_cardcode,vm_name,vm_sex,");
		sql.append(" vm_points");
		sql.append(" FROM t_vip_member t");
		sql.append(" JOIN t_base_shop s");
		sql.append(" ON s.sp_code=t.vm_shop_code");
		sql.append(" AND s.companyid=t.companyid");
		if(!CommonUtil.FOUR.equals(shop_type)){ // 如果登录的角色范围是自营店，则查询自营店下上级店铺相同所有自营店会员数据
			sql.append(getSellShopSQL(shop_uptype, 0));
		}else{
			sql.append(" AND sp_code=:shop_code");
		} 
		sql.append(" WHERE 1 = 1");
		sql.append(" AND (t.vm_cardcode=:vm_cardcode OR t.vm_mobile=:vm_cardcode)");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), param,new BeanPropertyRowMapper<>(T_Vip_Member.class));
		}catch(Exception e){
			return null;
		}
	}
	
	@Override
	public T_Vip_Member vipByMobile(Map<String, Object> param) {
		Object shop_type = param.get(CommonUtil.SHOP_TYPE);
		Object shop_uptype = param.get(CommonUtil.SHOP_UPTYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT vm_id,mt_discount,vm_mobile,vm_cardcode,vm_name,mt_name,");
		sql.append(" vm_points");
		sql.append(" FROM t_vip_member t");
		sql.append(" JOIN t_vip_membertype m");
		sql.append(" ON m.mt_code=t.vm_mt_code");
		sql.append(" AND m.companyid=t.companyid");
		sql.append(" JOIN t_base_shop s");
		sql.append(" ON s.sp_code=t.vm_shop_code");
		sql.append(" AND s.companyid=t.companyid");
		if(!CommonUtil.FOUR.equals(shop_type)){ // 如果登录的角色范围是自营店，则查询自营店下上级店铺相同所有自营店会员数据
			sql.append(getSellShopSQL(shop_uptype, 0));
		}else{
			sql.append(" AND sp_code=:shop_code");
		} 
		sql.append(" WHERE 1 = 1");
		sql.append(" AND (t.vm_cardcode=:mobile OR t.vm_mobile=:mobile)");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), param,new BeanPropertyRowMapper<>(T_Vip_Member.class));
		}catch(Exception e){
			return null;
		}
	}

	@Override
	public T_Vip_Member vipByCode(String code,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT vm_id,vm_code,vm_mobile,vm_cardcode,vm_name,vm_points");
		sql.append(" FROM t_vip_member t");
		sql.append(" WHERE 1 = 1");
		sql.append(" AND t.vm_code=:vm_code");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" LIMIT 1");
		try{
			Map<String,Object> param = new HashMap<String, Object>();
			param.put("vm_code", code);
			param.put("companyid", companyid);
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					param,
					new BeanPropertyRowMapper<>(T_Vip_Member.class));
		}catch(Exception e){
			return null;
		}
	}
	@Override
	public void updatePoint(Map<String, Object> param) {
		Object point = param.get("point");
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_vip_member");
		sql.append(" SET vm_points=vm_points+:point");
		if(NumberUtil.toInteger(point) > 0){
			sql.append(" ,vm_total_point=vm_total_point+:point");
		}
		sql.append(" WHERE vm_id=:vm_id");
		namedParameterJdbcTemplate.update(sql.toString(), param);
		//保存积分记录
		sql.setLength(0);
		sql.append("INSERT INTO t_vip_pointlist(");
		sql.append("vpl_vm_code,vpl_point,vpl_type,vpl_date,");
		sql.append("vpl_shop_name,vpl_manager,vpl_remark,companyid");
		sql.append(") VALUES (");
		sql.append(":vm_code,:point,:vpl_type,sysdate(),");
		sql.append(":shop_name,:emp_name,:vpl_remark,:companyid");
		sql.append(")");
		namedParameterJdbcTemplate.update(sql.toString(), param);
	}

	@SuppressWarnings("unchecked")
	@Override
	public void updateBySell(Map<String, Object> param) {
		T_Vip_Member vip = (T_Vip_Member)param.get(CommonUtil.KEY_VIP);
		String vip_point = StringUtil.trimString(param.get("vip_point"));
		String sh_point_money = StringUtil.trimString(param.get("sh_point_money"));
		String sell_type = StringUtil.trimString(param.get(CommonUtil.SELL_TYPE));
		Map<String,Object> setMap = (Map<String,Object>)param.get(CommonUtil.KEY_CASHIER_SET);
		double sh_lost_money = NumberUtil.toDouble(StringUtil.trimString(param.get("sh_lost_money")));
		param.put("vip_code", vip.getVm_code());
		param.put("vm_id", vip.getVm_id());
		param.put("use_point", NumberUtil.toInteger(vip_point));
		StringBuffer sql = new StringBuffer("");
		//保存会员积分抵用的记录
		if(null != vip_point && !"".equals(vip_point)){
			param.put("vpl_type", 9);
			param.put("vpl_remark", "会员使用积分"+vip_point+"抵用金额"+sh_point_money);
			param.put("vip_point", NumberUtil.toInteger(vip_point));
			saveVipList(param);
			param.put("vip_point",0);
		}
		//查询会员消费可以积分的金额
		sql.setLength(0);
		sql.append("SELECT ");
		sql.append(" SUM(sht_money) AS money ");
		sql.append(" FROM t_sell_shop_temp t");
		sql.append(" WHERE 1=1");
		sql.append(" AND t.sht_isputup=:putup");
		sql.append(" AND t.sht_ispoint=1");
		sql.append(" AND t.sht_vip_code=:vip_code");
		sql.append(" AND t.sht_em_code=:emp_code");
		sql.append(" AND t.sht_shop_code=:shop_code");
		sql.append(" AND t.companyid=:companyid");
		Double _sell_money = 0d,pointMoney = 0d;
		try {
			_sell_money = namedParameterJdbcTemplate.queryForObject(sql.toString(), param,Double.class);
		} catch (Exception e) {
		}
		param.put("sell_money", _sell_money);//记录会员消费次数与消费
		param.put("point", 0);
		if(_sell_money != 0){
			pointMoney = _sell_money-sh_lost_money;
			String vip_remark = "";
			if(pointMoney != 0){
				//取会员积分设置
				sql.setLength(0);
				sql.append(" SELECT ");
				sql.append(" mt_isauto_up,mt_min_point,mt_money,");
				sql.append(" mt_point,mt_point_to_money,");
				sql.append(" IFNULL(vm_points,0) last_point,IFNULL(vm_total_point,0) total_point");
				sql.append(" FROM t_vip_member t");
				sql.append(" JOIN t_vip_membertype m");
				sql.append(" ON t.vm_mt_code=m.mt_code");
				sql.append(" AND t.companyid=m.companyid");
				sql.append(" WHERE vm_id=:vm_id");
				sql.append(" LIMIT 1");
				T_Vip_MemberType type = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
						param, 
						new BeanPropertyRowMapper<>(T_Vip_MemberType.class));
				int point = NumberUtil.toInteger((pointMoney/type.getMt_money())*type.getMt_point());
				String vip_birthday = StringUtil.trimString(param.get("KEY_VIP_BIRTHDAY"));
				if("1".equals(vip_birthday)){
					if(vip.getIsbirthday().equals("1")){
						int point_times = NumberUtil.toInteger(StringUtil.trimString(setMap.get("KEY_POINT_TIMES")));
						point = point*(point_times == 0?1:point_times);
						vip_remark = "会员生日积分"+point_times+"倍;";
					}
				}
				vip_remark += "消费"+_sell_money+",积分"+pointMoney+",规则："+type.getMt_money()+"元积"+type.getMt_point()+"分";
				if("0".equals(sell_type)){
					param.put("vpl_type", 2);
				}
				if("1".equals(sell_type)){	
					param.put("vpl_type", 3);
				}
				if("2".equals(sell_type)){	
					param.put("vpl_type", 4);
				}
				param.put("point", point);
				param.put("vpl_remark",vip_remark);
				param.put("vip_point",point);
				saveVipList(param);
				updateVipMoney(param);
				//零售情况下，处理会员自动升级的功能
				if("0".equals(sell_type) && type.getMt_isauto_up() == 1){
					if((type.getTotal_point()+point) > type.getMt_min_point()){
						param.put("min_point", type.getMt_min_point());
						param.put("total_point", type.getTotal_point()+point);
						upVip(param);
					}
				}
			}else{
				updateVipMoney(param);
			}
		}else{
			updateVipMoney(param);
		}
	}
	public static void main(String[] args) {
		Double a = 48.0d;
		Integer b = 1;
		Integer c = 1;
		System.out.println(NumberUtil.toInteger(a/b*c));
	}
	private void upVip(Map<String,Object> param){
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT MAX(g.mt_min_point) AS mt_min_point,g.mt_code");
		sql.append(" FROM (");
		sql.append(" SELECT mt_min_point,mt_code");
		sql.append(" FROM t_vip_membertype t");
		sql.append(" WHERE mt_isauto_up=1");
		sql.append(" AND mt_shop_upcode=:shop_upcode");
		sql.append(" AND mt_min_point>:min_point");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" ) g");
		sql.append(" WHERE g.mt_min_point<=:total_point");
		T_Vip_MemberType _type = null;
		try {
			_type = namedParameterJdbcTemplate.queryForObject(sql.toString(), param, 
				new BeanPropertyRowMapper<>(T_Vip_MemberType.class));
		} catch (Exception e) {
		}
		if(null != _type && StringUtil.isNotEmpty(_type.getMt_code())){
			sql.setLength(0);
			sql.append(" UPDATE t_vip_member");
			sql.append(" SET vm_mt_code=:mt_code");
			sql.append(" WHERE vm_id=:vm_id");
			param.put("mt_code", _type.getMt_code());
			namedParameterJdbcTemplate.update(sql.toString(), param);
			//TODO 升级明细用处不大，暂时没加
		}
	}
	/**
	 * 更新会员的积分金额，消费次数，最后消费时间
	 * */
	private void updateVipMoney(Map<String,Object> param){
		String use_point = StringUtil.trimString(param.get("use_point"));
		String point = StringUtil.trimString(param.get("point"));
		int _use_point = NumberUtil.toInteger(use_point);
		int add_point = NumberUtil.toInteger(point)-_use_point;
		param.put("add_point", add_point);
		int times = 0;
		String sell_type = StringUtil.trimString(param.get(CommonUtil.SELL_TYPE));
		if("0".equals(sell_type)){
			times = 1;
		}
		if("1".equals(sell_type)){	
			times = -1;
		}
		param.put("times", times);
		param.put("date", DateUtil.getYearMonthDate());
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDAtE t_vip_member SET");
		sql.append(" vm_total_point=vm_total_point+:point,");
		sql.append(" vm_points=vm_points+:add_point,");
		sql.append(" vm_used_points=vm_used_points+:use_point,");
		sql.append(" vm_total_money=vm_total_money+:sell_money,");
		sql.append(" vm_times=IF(vm_times+:times > -1,vm_times+:times,0),");
		sql.append(" vm_lastbuy_date=:date,");
		sql.append(" vm_lastbuy_money=:sell_money");
		sql.append(" WHERE vm_id=:vm_id");
		namedParameterJdbcTemplate.update(sql.toString(), param);
	}
	/**
	 * 保存会员积分的明细
	 * */
	private void saveVipList(Map<String,Object> param){
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_vip_pointlist(");
		sql.append("vpl_vm_code,vpl_point,vpl_type,vpl_date,");
		sql.append("vpl_shop_name,vpl_manager,vpl_remark,companyid");
		sql.append(") VALUES (");
		sql.append(":vip_code,:vip_point,:vpl_type,sysdate(),");
		sql.append(":shop_name,:emp_name,:vpl_remark,:companyid");
		sql.append(")");
		namedParameterJdbcTemplate.update(sql.toString(), param);
	}

	@Override
	public List<T_Sell_ShopList> listSell(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer("");
		sql.append("select pd_no,pd_name,");
		sql.append("(SELECT cr_name from t_base_color t1 WHERE cr_code=shl_cr_code AND t.companyid=t1.companyid LIMIT 1) AS cr_name,");
		sql.append("(SELECT sz_name from t_base_size t1 WHERE sz_code=shl_sz_code AND t.companyid=t1.companyid LIMIT 1) AS sz_name,");
		sql.append("(SELECT br_name from t_base_bra t1 WHERE br_code=shl_br_code AND t.companyid=t1.companyid LIMIT 1) AS br_name,");
		sql.append("shl_amount,shl_price,shl_money,shl_date,");
		sql.append("(SELECT sp_name from t_base_shop t1 WHERE sp_code=shl_shop_code AND t.companyid=t1.companyid LIMIT 1) AS shop_name,");
		sql.append("(SELECT em_name from t_base_emp t1 WHERE em_code=shl_main AND t.companyid=t1.companyid LIMIT 1) AS main_name");
		sql.append(" from t_sell_shoplist t");
		sql.append(" JOIN t_base_product p");
		sql.append(" ON p.pd_code=shl_pd_code");
		sql.append(" AND p.companyid=t.companyid");
		sql.append(" where shl_vip_code=:vip_code");
		sql.append(" AND t.companyid=:companyid");
		sql.append(" ORDER BY shl_id DESC");
		sql.append(" LIMIT 10");
		
		return namedParameterJdbcTemplate.query(sql.toString(), param, 
				new BeanPropertyRowMapper<>(T_Sell_ShopList.class));
	}

	@Override
	public List<T_Vip_PonitList> listPoint(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT ");
		sql.append(" (SELECT dt_name FROM common_dict WHERE dt_code=vpl_type AND dt_type='VIP_POINT' LIMIT 1) dt_name,");
		sql.append(" vpl_point,vpl_type,vpl_remark,vpl_date,");
		sql.append(" vpl_shop_name,vpl_manager,companyid");
		sql.append(" FROM t_vip_pointlist");
		sql.append(" WHERE vpl_vm_code=:vip_code");
		sql.append(" AND companyid=:companyid");
		sql.append(" ORDER BY vpl_id DESC");
		sql.append(" LIMIT 10");
		return namedParameterJdbcTemplate.query(sql.toString(), param, 
				new BeanPropertyRowMapper<>(T_Vip_PonitList.class));
	}
	
	//------------------------接口---------------------------
	@Override
	public List<T_Vip_Member> list_server(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Object unconsume_days = params.get("unconsume_days");
		String unconsume_date = null;
		if(StringUtil.isNotEmpty(unconsume_days)){
			unconsume_date = DateUtil.getDateAddDays(-Integer.parseInt(unconsume_days.toString()));
			params.put("unconsume_date", unconsume_date);
		}
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT vm_id,vm_code,vm_mobile,vm_cardcode,vm_mt_code,vm_shop_code,vm_name,vm_password,vm_sex,");
		sql.append(" vm_birthday_type,vm_birthday,vm_lunar_birth,vm_state,vm_date,vm_enddate,vm_manager_code,t.companyid,");
		sql.append(" vm_points,vm_used_points,vm_init_points,vm_times,vm_total_money,vm_lastbuy_date,vm_lastbuy_money,vm_img_path,");
		sql.append(" sp_name AS shop_name,");
		sql.append(" (SELECT mt_name FROM t_vip_membertype mt WHERE mt_code = vm_mt_code AND mt.companyid = t.companyid LIMIT 1) AS mt_name,");
		sql.append(" (SELECT em_name FROM t_base_emp em WHERE em_code = vm_manager_code AND em.companyid = t.companyid LIMIT 1) AS vm_manager");
		sql.append(" FROM t_vip_member t");
		sql.append(" JOIN t_base_shop sp ON vm_shop_code = sp_code AND t.companyid = sp.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		
		if (StringUtil.isNotEmpty(params.get("vm_mt_code"))) {
			sql.append(" AND vm_mt_code = :vm_mt_code ");
		}
		if (StringUtil.isNotEmpty(params.get("vm_shop_code"))) {
			sql.append(" AND vm_shop_code = :vm_shop_code ");
		}
		if (StringUtil.isNotEmpty(params.get("vm_cardcode"))) {
			sql.append(" AND (INSTR(vm_cardcode, :vm_cardcode) > 0 OR INSTR(vm_mobile, :vm_cardcode) > 0)");
		}
		if (StringUtil.isNotEmpty(params.get("begindate"))) {
			sql.append(" AND vm_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(params.get("enddate"))) {
			sql.append(" AND vm_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(params.get("lastbuy_begindate"))) {
			sql.append(" AND vm_lastbuy_date >= :lastbuy_begindate ");
		}
		if (StringUtil.isNotEmpty(params.get("lastbuy_enddate"))) {
			sql.append(" AND vm_lastbuy_date <= :lastbuy_enddate ");
		}
		if (StringUtil.isNotEmpty(params.get("consume_money_min"))) {
			sql.append(" AND vm_total_money >= :consume_money_min ");
		}
		if (StringUtil.isNotEmpty(params.get("consume_money_max"))) {
			sql.append(" AND vm_total_money <= :consume_money_max ");
		}
		if (StringUtil.isNotEmpty(params.get("consume_times_min"))) {
			sql.append(" AND vm_times >= :consume_times_min ");
		}
		if (StringUtil.isNotEmpty(params.get("consume_times_max"))) {
			sql.append(" AND vm_times <= :consume_times_max ");
		}
		if(StringUtil.isNotEmpty(unconsume_date)){
			sql.append(" AND (vm_lastbuy_date <= :unconsume_date OR (vm_lastbuy_date IS NULL AND vm_date <= :unconsume_date) )");
		}
		sql.append(" AND t.companyid=:companyid");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY vm_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Vip_Member.class));
	}
	
	@Override
	public T_Vip_Member search(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT vm_id,vm_code,vm_mobile,vm_cardcode,vm_mt_code,vm_shop_code,vm_name,vm_password,vm_sex,");
		sql.append(" vm_birthday_type,vm_birthday,vm_lunar_birth,vm_state,vm_date,vm_enddate,vm_manager_code,t.companyid,");
		sql.append(" vm_points,vm_used_points,vm_init_points,vm_times,vm_total_money,vm_lastbuy_date,vm_lastbuy_money,vm_img_path,");
		sql.append(" sp_name AS shop_name,");
		sql.append(" (SELECT mt_name FROM t_vip_membertype mt WHERE mt_code = vm_mt_code AND mt.companyid = t.companyid LIMIT 1) AS mt_name,");
		sql.append(" (SELECT em_name FROM t_base_emp em WHERE em_code = vm_manager_code AND em.companyid = t.companyid LIMIT 1) AS vm_manager");
		sql.append(" FROM t_vip_member t");
		sql.append(" JOIN t_base_shop sp ON vm_shop_code = sp_code AND t.companyid = sp.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(" AND (t.vm_cardcode=:vm_cardcode OR t.vm_mobile=:vm_cardcode)");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), params,
					new BeanPropertyRowMapper<>(T_Vip_Member.class));
		}catch(Exception e){
			return null;
		}
	}
	
}
